Zikula Community

• dits
  
  

  • Rank: Developer
  • Registered: Feb 17, 2005
  • Last visit: May 21, 2010
  • Posts: 684

  Posted: Oct 12, 2009 - 08:39 PM

  One minor change to the patch above:
  

  Code

  // build the where clause
          $where = array();
          $count = 1;
          foreach ($categoryFilter as $property => $category) {
              // this allows to have an array of categories IDs
              if (is_array($category)) {
                  $wherecat = array();
                  foreach ($category as $cat) {
                      $wherecat[] = "mapobj".$count.".cmo_category_id='".DataUtil::formatForStore($cat)."'";
                  }
                  $wherecat = '('.implode(' OR ', $wherecat).')';
  
              // if there's only one category ID
              } else {
                  $wherecat = "mapobj".$count.".cmo_category_id='".DataUtil::formatForStore($category)."'";
              }
              $where[] = "cmo_obj_id IN (SELECT mapobj".$count.".cmo_obj_id FROM pn_categories_mapobj mapobj".$count." WHERE mapobj".$count.".cmo_reg_id = '".DataUtil::formatForStore($propids[$property])."' AND ".$wherecat.")";
              $count++;
          }
          $where  = "cmo_table='".DataUtil::formatForStore($tablename)."' AND (".implode(' AND ', $where).")";

  
  
  Any category experts to comment out there?

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Oct 13, 2009 - 01:07 PM

  One issue:
  I don't understand why the "mapobj{$count}.cmo_category_id" if there's only one 'categories_mapobj' table.
  
  And why subqueries searching obj_ids if the whole _generateCategoryFilter is supposed to search for the objIds that are under the categoryFilter criteria.
  
  I guess that we just can make flexible the last OR:
  

  Code

  $where = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND (" . implode(' OR ', $where) . ')';

  then have a

  Code

  $categoryFilter['__META__']['operator'] = and/or

  to use there.
  
  Then you can match the objIds that have all the registries/categories in your filter (AND), or one at minimum (OR).
  So, what to do?
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• dits
  
  

  • Rank: Developer
  • Registered: Feb 17, 2005
  • Last visit: May 21, 2010
  • Posts: 684

  Posted: Oct 13, 2009 - 07:29 PM

  Hi Mateo,
  
  I tried exactly what you suggested above, it's a few posts back in this same thread. Somehow it did not give the results I expected. I can't remember the exact behaviour, but I'll check it and I will report it here. Putting the operator in the meta data of the filter would be an excellent idea.
  
  The messy patch, using the sub-query (and the count), was because the change you suggested did not work. Of course it would be preferable if that could work. Let me first go back and check exactly what went wrong there. Thanks for your help in this. It's highly appreciated!

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Oct 13, 2009 - 08:07 PM

  You're welcome Bert,
  i'm busy updating extdb but i can have an eye checking your results here
  
  My idea there was to extract from the categories_mapobj table, the IDs of the objects matching the registry => categories array, and well, i guess that changing the final OR to AND will require to be on all the Registries listed in the $categoryFilter, a query like:
  
  

  Code

  select cmo_obj_id
  from zk_categories_mapobj
  where
      cmo_table='stories' and (
          (cmo_reg_id='2' and (cmo_category_id='31' or cmo_category_id='32'))
          and
          (cmo_reg_id='3' and cmo_category_id='33')
      )

  Now, the AND between registries makes no sense...
  
  We may need many queries in that case and intersect results
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• dits
  
  

  • Rank: Developer
  • Registered: Feb 17, 2005
  • Last visit: May 21, 2010
  • Posts: 684

  Posted: Oct 13, 2009 - 08:40 PM

  That's exactly what is happening. In my specific it produces the following where clause:
  

  Code

  cmo_table='addressbook_address' AND
  ((cmo_reg_id='16' AND (cmo_category_id='10034')) AND
   (cmo_reg_id='17' AND (cmo_category_id='10038')))

  
  The patched version produces:

  Code

  cmo_table='addressbook_address' AND
  (cmo_obj_id IN (SELECT mapobj1.cmo_obj_id
                  FROM pn_categories_mapobj mapobj1
                  WHERE mapobj1.cmo_reg_id = '16' AND (mapobj1.cmo_category_id='10034')) AND
   cmo_obj_id IN (SELECT mapobj2.cmo_obj_id
                  FROM pn_categories_mapobj mapobj2
                  WHERE mapobj2.cmo_reg_id = '17' AND (mapobj2.cmo_category_id='10038')))

  
  Not pretty. I was hoping you could come up with a more elegant solution

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Oct 13, 2009 - 09:29 PM

  Well, i don't know if i like subqueries (i guess not )
  but, what do you think of this?
  

  Code

  // check the meta data
          if (isset($categoryFilter['__META__']['module'])) {
              $modname = $categoryFilter['__META__']['module'];
          } else {
              $modname = pnModGetName();
          }
  
          // check the meta data
          if (isset($categoryFilter['__META__']['operator']) && in_array(strtolower($categoryFilter['__META__']['operator']), array('and', 'or'))) {
              $op = strtoupper($categoryFilter['__META__']['operator']);
          } else {
              $op = 'OR';
          }
  
          unset($categoryFilter['__META__']);
  
          // get the properties IDs in the category register
          Loader::loadClass('CategoryRegistryUtil');
          $propids = CategoryRegistryUtil::getRegisteredModuleCategoriesIds($modname, $tablename);
  
          // build the where clause
          $where = array();
          foreach ($categoryFilter as $property => $category) {
              // this allows to have an array of categories IDs
              if (is_array($category)) {
                  $wherecat = array();
                  foreach ($category as $cat) {
                      $wherecat[] = "cmo_category_id='" . DataUtil::formatForStore($cat) . "'";
                  }
                  $wherecat = '(' . implode(' OR ', $wherecat) . ')';
  
              // if there's only one category ID
              } else {
                  $wherecat = "cmo_category_id='" . DataUtil::formatForStore($category) . "'";
              }
              $where[] = "(cmo_reg_id='" . DataUtil::formatForStore($propids[$property]) . "' AND $wherecat)";
          }
  
          if ($op == 'OR') {
              $where = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND (" . implode(' OR ', $where) . ')';
              // perform the query
              $objIds = DBUtil::selectFieldArray('categories_mapobj', 'obj_id', $where);
          } else {
              $objIds = array();
              foreach ($where as $wh) {
                  $wh = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND $wh";
                  $objIds[] = DBUtil::selectFieldArray('categories_mapobj', 'obj_id', $wh);
              }
              $numresults = count($objIds);
              if ($numresults > 1) {
                  for ($i = 0; $i < $numresults-1; $i++) {
                      $objIds[$i+1] = array_intersect($objIds[$i], $objIds[$i+1]);
                  }
                  $objIds = $objIds[$numresults];
              } else {
                  $objIds = $objIds[0];
              }
          }

  Tested only in my head
  
  New: operator detector ($op) and different treatment for AND, multiple queries, one per registry in the $categoryFilter, then intersect the results. But well, if we think on optimization, subqueries are the best way...
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• dits
  
  

  • Rank: Developer
  • Registered: Feb 17, 2005
  • Last visit: May 21, 2010
  • Posts: 684

  Posted: Oct 13, 2009 - 09:44 PM

  Need to go in a meeting just now. I'll test your code later today. It looks nice, clean and logical, but from an optimisation perspective it doesn't look optimal and perhaps it can be a bit shorter/simpler somehow. I'll try some more tonight. Thanks again, and don't let me keep you of the extdb any longer for now
  
  
  
  edited by: dits, Oct 14, 2009 - 11:44 AM

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Oct 22, 2009 - 07:28 PM

  Ok Bert, please test asap my commit on DBUtil: changeset 27114, i guess it's kind of clean and I guess that you verified that subqueries works as $where parameter of DBUtil
  
  Greetings!
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• dits
  
  

  • Rank: Developer
  • Registered: Feb 17, 2005
  • Last visit: May 21, 2010
  • Posts: 684

  Posted: Oct 22, 2009 - 09:55 PM

  Thanks Mateo!
  
  I tested the patch and got some SQL errors. I've slightly modified it as follows:
  

  Code

  $where = array();
          foreach ($categoryFilter as $property => $category)
          {
              $prefix = '';
              if ($op == 'AND') {
                  $prefix = "table$n";
                  $n++;
              }
  
              // this allows to have an array of categories IDs
              if (is_array($category)) {
                  $wherecat = array();
                  foreach ($category as $cat) {
                      if ($prefix) {
                          $wherecat[] = "{$prefix}.cmo_category_id='" . DataUtil::formatForStore($cat) . "'";
                      } else {
                          $wherecat[] = "cmo_category_id='" . DataUtil::formatForStore($cat) . "'";
                      }
                  }
                  $wherecat = '(' . implode(' OR ', $wherecat) . ')';
  
              // if there's only one category ID
              } else {
                  $wherecat = "{$prefix}.cmo_category_id='" . DataUtil::formatForStore($category) . "'";
              }
  
              // process the where depending of the operator
              if ($op == 'AND') {
                  $where[] = "cmo_obj_id IN (SELECT {$prefix}.cmo_obj_id FROM $catmapobjtbl $prefix WHERE {$prefix}.cmo_reg_id = '".DataUtil::formatForStore($propids[$property])."' AND $wherecat)";
              } else {
                  $where[] = "(cmo_reg_id='" . DataUtil::formatForStore($propids[$property]) . "' AND $wherecat)";
              }
          }
          $where = "cmo_table='" . DataUtil::formatForStore($tablename) . "' AND (" . implode(" $op ", $where) . ')';

  
  
  And now it works like a dream.

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Oct 23, 2009 - 06:36 PM

  Thanks a lot Bert!
  I knew I've forgotten some pieces and it was true. The point goes into the prefix to avoid SQL problems when the operator is OR. I've commited (27130), and I guess it's complete now
  
  Thanks to you Bert
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
•  
•  

Users on-line

• eledril

This list is based on users active over the last 60 minutes.