Forum
Filtering by multiple categories, category-match 'all'
Perch Runway: 3.1.1, PHP: 7.1.17, MySQL: 5.6.26, with PDO
Server OS: Linux, cgi-fcgi
Installed apps: content (3.1.1), assets (3.1.1), categories (3.1.1), perch_blog (5.6.1), perch_events (1.9.5)
App runtimes: <?php $apps_list = [ 'perch_events', 'perch_blog', ]; ?>
PERCH_LOGINPATH: /perch
PERCH_PATH: /home/****/public_html/perch
PERCH_CORE: /home/****/public_html/perch/core
PERCH_RESFILEPATH: /home/****/public_html/perch/resources
Image manipulation: GD
PHP limits: Max upload 256M, Max POST 8M, Memory: 128M, Total max file upload: 8M
F1: 3b606135b33e6a102526838f4152a807
Resource folder writeable: Yes
DOCUMENT_ROOT: /home/****/public_html
HTTP_HOST: www.****.org
REQUEST_URI: /perch/core/settings/diagnostics/
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
Hello,
I'm having an issue with filtering a collection by multiple categories - it works well by default (so where it looks to see if the item has 'any' of the categories), but the filter fails when I force category-match => all
The collection call:
$locations = perch_collection('Directory Locations', [
'template' => 'directory/directory-list.html',
'sort-order' => 'DESC',
'category' => ['%/living-safely-at-home','%/oxford'],
'category-match' => 'all',
'skip-template' => true,
'return-html' => true,
]);
The SQL query that is produced:
SELECT idx.itemID FROM perch2_collection_index idx JOIN perch2_collection_revisions c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.collectionID=c.collectionID WHERE (idx.collectionID=5) AND ((idx.indexKey='_category' AND idx.indexValue LIKE '%/living-safely-at-home%') OR (idx.indexKey='_category' AND idx.indexValue LIKE '%/oxford%')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=2
It feels like the same issue that resulted in you providing the 'filter-mode' => 'ungrouped'
when using multiple filters - it's the HAVING COUNT that isn't working as expected.
I'll take a look and see what I can find.
Drew,
I may need to look at this myself a bit more, but it seems that having a first level category and sub-categories assigned to an item causes the issue.
if an item has:
category1/subcatA
category1/subcatB
category2/subcatH
category2/subcatI
assigned to it, the filtering works OK.
As soon as an item has:
category1
category1/subcatA
category1/subcatB
assigned to it, the filtering doesn't seem to work.
I may have time to try a few combinations, but it seems as it the cause of the problem is this...
If an item is in
category1/subcatA
then it's automatically incategory1
too, so that scenario shouldn't need to occur.I agree - however, having been able to cause an issue like this, I can almost guarantee that an editor of the site will unwittingly do the same.
I can foresee an editor applying a category to an item, then later adding subcategories to that category, going back into an item and adding the new subcategory - but forgetting to remove the original category... The kind of thing that happens way down the line and causes confusion for everyone!
Yes, I can see how that might happen.