source: trunk/include/functions_tag.inc.php @ 4334

Last change on this file since 4334 was 4334, checked in by nikrou, 14 years ago

Feature 1255 : limit params in sql queries were in wrong order (LIMIT count OFFSET offset)

  • Property svn:eol-style set to LF
File size: 7.6 KB
Line 
1<?php
2// +-----------------------------------------------------------------------+
3// | Piwigo - a PHP based picture gallery                                  |
4// +-----------------------------------------------------------------------+
5// | Copyright(C) 2008-2009 Piwigo Team                  http://piwigo.org |
6// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
7// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
8// +-----------------------------------------------------------------------+
9// | This program is free software; you can redistribute it and/or modify  |
10// | it under the terms of the GNU General Public License as published by  |
11// | the Free Software Foundation                                          |
12// |                                                                       |
13// | This program is distributed in the hope that it will be useful, but   |
14// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
15// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
16// | General Public License for more details.                              |
17// |                                                                       |
18// | You should have received a copy of the GNU General Public License     |
19// | along with this program; if not, write to the Free Software           |
20// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
21// | USA.                                                                  |
22// +-----------------------------------------------------------------------+
23
24
25/**
26 * Tags available. Each return tag is represented as an array with its id,
27 * its name, its weight (count), its url name. Tags are not sorted.
28 *
29 * The returned list can be a subset of all existing tags due to
30 * permissions, only if a list of forbidden categories is provided
31 *
32 * @param array forbidden categories
33 * @return array
34 */
35function get_available_tags()
36{
37  // we can find top fatter tags among reachable images
38  $query = '
39SELECT tag_id, COUNT(DISTINCT(it.image_id)) counter
40  FROM '.IMAGE_CATEGORY_TABLE.' ic
41    INNER JOIN '.IMAGE_TAG_TABLE.' it ON ic.image_id=it.image_id'.get_sql_condition_FandF
42    (
43      array
44        (
45          'forbidden_categories' => 'category_id',
46          'visible_categories' => 'category_id',
47          'visible_images' => 'ic.image_id'
48        ),
49      '
50  WHERE'
51    ).'
52  GROUP BY tag_id
53  ORDER BY NULL';
54  $tag_counters = simple_hash_from_query($query, 'tag_id', 'counter');
55
56  if ( empty($tag_counters) )
57  {
58    return array();
59  }
60
61  $query = '
62SELECT *
63  FROM '.TAGS_TABLE;
64  $result = pwg_query($query);
65  $tags = array();
66  while ($row = pwg_db_fetch_assoc($result))
67  {
68    $counter = @$tag_counters[ $row['id'] ];
69    if ( $counter )
70    {
71      $row['counter'] = $counter;
72      array_push($tags, $row);
73    }
74  }
75  return $tags;
76}
77
78/**
79 * All tags, even tags associated to no image.
80 *
81 * @return array
82 */
83function get_all_tags()
84{
85  $query = '
86SELECT *
87  FROM '.TAGS_TABLE.'
88;';
89  $result = pwg_query($query);
90  $tags = array();
91  while ($row = pwg_db_fetch_assoc($result))
92  {
93    array_push($tags, $row);
94  }
95
96  usort($tags, 'tag_alpha_compare');
97
98  return $tags;
99}
100
101/**
102 * Giving a set of tags with a counter for each one, calculate the display
103 * level of each tag.
104 *
105 * The level of each tag depends on the average count of tags. This
106 * calcylation method avoid having very different levels for tags having
107 * nearly the same count when set are small.
108 *
109 * @param array tags
110 * @return array
111 */
112function add_level_to_tags($tags)
113{
114  global $conf;
115
116  if (count($tags) == 0)
117  {
118    return $tags;
119  }
120
121  $total_count = 0;
122
123  foreach ($tags as $tag)
124  {
125    $total_count+= $tag['counter'];
126  }
127
128  // average count of available tags will determine the level of each tag
129  $tag_average_count = $total_count / count($tags);
130
131  // tag levels threshold calculation: a tag with an average rate must have
132  // the middle level.
133  for ($i = 1; $i < $conf['tags_levels']; $i++)
134  {
135    $threshold_of_level[$i] =
136      2 * $i * $tag_average_count / $conf['tags_levels'];
137  }
138
139  // display sorted tags
140  foreach (array_keys($tags) as $k)
141  {
142    $tags[$k]['level'] = 1;
143
144    // based on threshold, determine current tag level
145    for ($i = $conf['tags_levels'] - 1; $i >= 1; $i--)
146    {
147      if ($tags[$k]['counter'] > $threshold_of_level[$i])
148      {
149        $tags[$k]['level'] = $i + 1;
150        break;
151      }
152    }
153  }
154
155  return $tags;
156}
157
158/**
159 * return the list of image ids corresponding to given tags. AND & OR mode
160 * supported.
161 *
162 * @param array tag ids
163 * @param string mode
164 * @return array
165 */
166function get_image_ids_for_tags($tag_ids, $mode = 'AND')
167{
168  switch ($mode)
169  {
170    case 'AND':
171    {
172      // strategy is to list images associated to each tag
173      $tag_images = array();
174
175      foreach ($tag_ids as $tag_id)
176      {
177        $query = '
178SELECT image_id
179  FROM '.IMAGE_TAG_TABLE.'
180  WHERE tag_id = '.$tag_id.'
181;';
182        $tag_images[$tag_id] = array_from_query($query, 'image_id');
183      }
184
185      // then we calculate the intersection, the images that are associated to
186      // every tags
187      $items = array_shift($tag_images);
188      foreach ($tag_images as $images)
189      {
190        $items = array_intersect($items, $images);
191      }
192      return $items;
193      break;
194    }
195    case 'OR':
196    {
197      $query = '
198SELECT DISTINCT image_id
199  FROM '.IMAGE_TAG_TABLE.'
200  WHERE tag_id IN ('.implode(',', $tag_ids).')
201;';
202      return array_from_query($query, 'image_id');
203      break;
204    }
205    default:
206    {
207      die('get_image_ids_for_tags: unknown mode, only AND & OR are supported');
208    }
209  }
210}
211
212/**
213 * return a list of tags corresponding to given items.
214 *
215 * @param array items
216 * @param array max_tags
217 * @param array excluded_tag_ids
218 * @return array
219 */
220function get_common_tags($items, $max_tags, $excluded_tag_ids=null)
221{
222  if (empty($items))
223  {
224    return array();
225  }
226  $query = '
227SELECT t.*, count(*) counter
228  FROM '.IMAGE_TAG_TABLE.'
229    INNER JOIN '.TAGS_TABLE.' t ON tag_id = id
230  WHERE image_id IN ('.implode(',', $items).')';
231  if (!empty($excluded_tag_ids))
232  {
233    $query.='
234    AND tag_id NOT IN ('.implode(',', $excluded_tag_ids).')';
235  }
236  $query .='
237  GROUP BY tag_id';
238  if ($max_tags>0)
239  {
240    $query .= '
241  ORDER BY counter DESC
242  LIMIT '.$max_tags;
243  }
244  else
245  {
246    $query .= '
247  ORDER BY NULL';
248  }
249
250  $result = pwg_query($query);
251  $tags = array();
252  while($row = pwg_db_fetch_assoc($result))
253  {
254    array_push($tags, $row);
255  }
256  usort($tags, 'tag_alpha_compare');
257  return $tags;
258}
259
260/**
261 * return a list of tags corresponding to any of ids, url_names, names
262 *
263 * @param array ids
264 * @param array url_names
265 * @param array names
266 * @return array
267 */
268function find_tags($ids, $url_names=array(), $names=array() )
269{
270  $where_clauses = array();
271  if ( !empty($ids) )
272  {
273    $where_clauses[] = 'id IN ('.implode(',', $ids).')';
274  }
275  if ( !empty($url_names) )
276  {
277    $where_clauses[] =
278      'url_name IN ('.
279      implode(
280        ',',
281        array_map(
282          create_function('$s', 'return "\'".$s."\'";'),
283          $url_names
284          )
285        )
286      .')';
287  }
288  if ( !empty($names) )
289  {
290    $where_clauses[] =
291      'name IN ('.
292      implode(
293        ',',
294        array_map(
295          create_function('$s', 'return "\'".$s."\'";'),
296          $names
297          )
298        )
299      .')';
300  }
301  if (empty($where_clauses))
302  {
303    return array();
304  }
305
306  $query = '
307SELECT *
308  FROM '.TAGS_TABLE.'
309  WHERE '. implode( '
310    OR ', $where_clauses);
311
312  $result = pwg_query($query);
313  $tags = array();
314  while ($row = pwg_db_fetch_assoc($result))
315  {
316    array_push($tags, $row);
317  }
318  return $tags;
319}
320?>
Note: See TracBrowser for help on using the repository browser.