[dev] Category - getCategoriesByAttributes

Mikael Geijer mikael at nufort.net
Fri Oct 31 00:47:02 PST 2003


Hi!
Method getCategoriesByAttributes doesn't group on the categorys 
attribute_name. If filter on timestamp and action, you will get hit's on 
all action-entries that matches the timestamp criteria. An example:

SELECT DISTINCT a1.category_id, c.category_name FROM horde_categories c 
LEFT JOIN horde_category_attributes a1 ON a1.category_id = c.category_id 
LEFT JOIN horde_category_attributes a2 ON a2.category_id = c.category_id 
LEFT JOIN horde_category_attributes a3 ON a3.category_id = c.category_id 
LEFT JOIN horde_category_attributes a4 ON a4.category_id = c.category_id 
WHERE c.group_uid = 'horde.history' AND (a1.attribute_key = 'ts' AND 
a1.attribute_value > '1067549100' AND a2.attribute_key = 'action' AND 
a2.attribute_value = 'delete' AND a3.attribute_key = 'who' AND 
a3.attribute_value = 'nisse' AND a4.attribute_key = 'ts' AND 
a4.attribute_value < 1067549176)

Will also match a modify action within the given timestamps (if there is 
one..).

I made a patch for lib/Category/sql.php that also singles out the 
timestamp. There might be more elegant way of doing it, the SQL is 
getting pretty complex..

Cheers

/Mikael

703d702
<           $attnames[] = 'AND a1.attribute_name = a' . $i . 
'.attribute_name';
706d704
<       $attnames = implode(' ',$attnames);
709c707
<                          ' WHERE c.group_uid = %s AND %s%s %s',
---
 >                          ' WHERE c.group_uid = %s AND %s%s',
714,715c712
<                          $levelQuery,
<                        $attnames);
---
 >                          $levelQuery);


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=- Mikael Geijer, nufort HB (nufort.net), Oebb AB (oebb.net)
=- mobile: +46 70 7455239 office +46 70 7455239
=- email:  mikael at nufort.net
=- snail:  Kyrklunden 8, 122 32 Enskede, Sweden
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-=-=

-------------- next part --------------
703d702
< 	    $attnames[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
706d704
< 	$attnames = implode(' ',$attnames);
709c707
<                          ' WHERE c.group_uid = %s AND %s%s %s',
---
>                          ' WHERE c.group_uid = %s AND %s%s',
714,715c712
<                          $levelQuery,
< 			 $attnames);
---
>                          $levelQuery);


More information about the dev mailing list