Forum

Thread tagged as: Problem, Runway

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.

Duncan Revell

Duncan Revell 78 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

I'll take a look and see what I can find.

Duncan Revell

Duncan Revell 78 points
Registered Developer

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...

Drew McLellan

Drew McLellan 2638 points
Perch Support

If an item is in category1/subcatA then it's automatically in category1 too, so that scenario shouldn't need to occur.

Duncan Revell

Duncan Revell 78 points
Registered Developer

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!

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, I can see how that might happen.