Multiple filters not returning any content

  • I'm having a problem where I need to use more than one filter but when I do it's not returning any content. Individually, the filters work fine, but when I combine them using the multiple filter syntax for perch_content_custom (I'm using perch_blog_custom, but this should translate) they don't work. Here's the code that isn't working with the code that does work in comments:



    I can't see any syntax issues (although there's a typo in the first example in the docs: a closing rounded parenthesis instead of a closing square bracket).


    This thread from late last year mentions the same issue but goes quiet.


    Here's what Perch Debug outputs:


    0 get(): ">[59] SELECT DISTINCT settingID, settingValue FROM perch2_settings WHERE userID=0
    0.0131 find_by_path(): ">[1] SELECT * FROM perch2_pages WHERE pagePath='/spotlight/post.php' LIMIT 1
    0.0033 get_navigation(): ">[6] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
    0.0005 get_value(): ">[1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/spotlight/post.php' LIMIT 1
    0.0004 find_parent_page_ids_by_path(): ">[2] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-005-001', '000-005', '000') ORDER BY pageTreePosition DESC
    0.0004 _template_nav(): ">[6] Using template: /templates/navigation/item.html
    0.0167 find_by_slug(): ">[1] SELECT * FROM perch2_blog_posts WHERE postStatus='Published' AND postDateTime<='2020-03-08 09:26:00' AND postSlug='driving-fintech-adoption'
    0.0106 all(): ">[1] SELECT * FROM perch2_blog_sections ORDER BY sectionTitle ASC
    0.0009 all(): ">[1] SELECT * FROM perch2_blogs ORDER BY blogTitle ASC
    0.002 set(): ">Using template: /templates/blog/post.html
    0.0024 get_filtered_listing_from_index(): ">[1] SELECT SQL_CALC_FOUND_ROWS DISTINCT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_blog_index idx JOIN perch2_blog_posts main ON idx.itemID=main.postID AND idx.itemKey='postID' JOIN perch2_blog_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='postID' AND idx2.indexKey='_id' WHERE 1=1 AND ((idx.indexKey='postSlug' AND idx.indexValue='driving-fintech-adoption')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, postID ) as tbl WHERE (postStatus='Published' AND postDateTime<='2020-03-08 09:26:00' ) GROUP BY itemID, sortval ORDER BY sortval ASC LIMIT 0, 10
    0.0039 get_value(): ">[1] SELECT FOUND_ROWS() AS `count`
    0.0004 set(): ">[1] Using template: /templates/blog/post.html
    0.0008 all(): ">[16] SELECT * FROM perch2_blog_authors ORDER BY authorFamilyName, authorGivenName ASC
    0.0036 get_filtered_listing_from_index(): ">[nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_blog_index idx JOIN perch2_blog_posts main ON idx.itemID=main.postID AND idx.itemKey='postID' JOIN perch2_blog_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='postID' AND idx2.indexKey='postDateTime' WHERE 1=1 AND ((idx.indexKey='spotlight' AND idx.indexValue='spotlight') OR (idx.indexKey='postSlug' AND idx.indexValue != 'driving-fintech-adoption')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, postID ) as tbl WHERE (postStatus='Published' AND postDateTime<='2020-03-08 09:26:00' ) GROUP BY itemID, sortval HAVING count(*)=2 ORDER BY sortval DESC LIMIT 0, 3
    0.0606 set(): ">Using template: /templates/blog/post_in_list.html
    0.0002 use_noresults(): ">Rescoping to perch:blog
    0 use_noresults(): ">Using sub-template: /templates/pagination/blog.html
    0.0031 get_custom(): ">[1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='Call to action' AND (regionPage='/spotlight/post.php' OR regionPage='*')
    0.0006 get_custom(): ">[1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=115 AND idx.itemRev=4)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval, regionID ORDER BY sortval ASC
    0.0023 get_custom(): ">[1] Using template: /templates/content/introduction.html
    0.0063 get_value(): ">[1] SELECT groupID FROM perch2_navigation WHERE groupSlug='useful-links' LIMIT 1
    0.0007 get_navigation(): ">[9] SELECT np.pageID, np.pageParentID, p.pagePath, p.pageTitle, p.pageNavText, p.pageNew, p.pageOrder, np.pageDepth, p.pageSortPath, np.pageTreePosition, p.pageAccessTags, p.pageAttributes FROM perch2_navigation_pages np, perch2_pages p WHERE p.pageID=np.pageID AND np.groupID=1 AND p.pageNew=0 AND np.pageDepth >=0 AND np.pageDepth<=1 ORDER BY np.pageTreePosition ASC
    0.0105 get_value(): ">[0] SELECT np.pageTreePosition FROM perch2_pages p, perch2_navigation_pages np WHERE np.pageID=p.pageID AND np.groupID=1 AND p.pagePath='/spotlight/post.php' LIMIT 1
    0.0023 _template_nav(): ">[9] Using template: /templates/navigation/item.html
    0.006 get_custom(): ">[1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='Primary content' AND (regionPage='/contact/index.php' OR regionPage='*')
    0.0008 get_custom(): ">[1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=22 AND idx.itemRev=13)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval, regionID ORDER BY sortval ASC
    0.0026 get_custom(): ">[1] Using template: /templates/content/_address.html
    0.0027 get_custom(): ">[1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=22 AND idx.itemRev=13)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval, regionID ORDER BY sortval ASC
    0.0045 get_custom(): ">[1] Using template: /templates/content/_contact_details.html
    0.0031 _get_content(): ">[3] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/spotlight/post.php' OR regionPage='*' ORDER BY regionPage DESC


    Any my diagnostics:

    • Perch: 3.1.5, PHP: 7.2.22, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $, with PDO
    • Server OS: Darwin, apache2handler
    • Installed apps: content (3.1.5), categories (3.1.5), assets (3.1.5), perch_blog (5.6.1), perch_forms (1.12), perch_members (1.6.4), perch_backup (1.2)
    • App runtimes: <?php $apps_list = [ 'perch_forms', 'perch_blog', 'perch_members', ];
    • PERCH_LOGINPATH: /cms
    • PERCH_PATH: /Users/martin/Sites/gustoresearch.com/web/cms
    • PERCH_CORE: /Users/martin/Sites/gustoresearch.com/web/cms/core
    • PERCH_RESFILEPATH: /Users/martin/Sites/gustoresearch.com/web/cms/resources
    • Image manipulation: GD
    • PHP limits: Max upload 32M, Max POST 8M, Memory: 128M, Total max file upload: 8M
    • F1: 3b606135b33e6a102526838f4152a807
    • Resource folder writeable: Yes
    • HTTP_HOST: gustoresearch.local
    • DOCUMENT_ROOT: /Users/martin/Sites/gustoresearch.com/web
    • REQUEST_URI: /cms/core/settings/diagnostics/
    • SCRIPT_NAME: /cms/core/settings/diagnostics/index.php


    Please help!

  • This isn't the only site I'm having this problem with. It's also happening on a Runway site with perch_collection, following the Multiple filters syntax. Here's the code that works in a slightly different format, to show that I've tried a few different ways. Again, this works, but when a second filter is added it outputs nothing:


    Code
    1. perch_collection('Posts', [ 'filter' => [ // [ // This works individually, but not when the subsequent filter is active // 'filter' => 'post_date', // 'match' => 'lte', // 'value' => date('Y-m-d H:i:s') // ], [ 'filter' => 'post_date', 'match' => 'eq', 'value' => perch_get('filter_year') ] ], 'template' => 'post.html', 'sort' => 'post_date', 'sort-order' => 'DESC'
    2. ]);

    I need to filter by year and also not output any posts from the future (so my client can front-load posts).

    Here's the debug:


    0 get_route(): ">[6] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate, pr.routeOrder, s.settingValue AS siteOffline FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID LEFT JOIN perch2_settings s ON s.settingID='siteOffline' UNION SELECT NULL AS pagePath, pr2.routePattern, pr2.routeRegExp, pr2.templatePath AS pageTemplate, pr2.routeOrder, NULL AS siteOffline FROM perch2_page_routes pr2 WHERE templateID!=0 ORDER BY routeOrder ASC, pagePath ASC
    0.0019 get_route(): ">Matched route: [year:filter_year]
    0.0001 Using master page: /templates/pages/news.php
    0 Page arguments:
    Code
    1. Array
    2. ( [0] => /2020 [filter_year] => 2020 [1] => 2020
    3. )
    0.0067 find_by_path(): ">[1] SELECT * FROM perch2_pages WHERE pagePath='/news' LIMIT 1
    0.0024 template_attribute(): ">Using template: /templates/pages/attributes/default.html
    0.0182 get_navigation(): ">[5] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
    0.0004 get_value(): ">[1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/news' LIMIT 1
    0.0002 find_parent_page_ids_by_path(): ">[1] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-003', '000') ORDER BY pageTreePosition DESC
    0.0002 _template_nav(): ">[5] Using template: /templates/navigation/item.html
    0.0014 get_collection(): ">[1] SELECT collectionID, collectionTemplate FROM perch2_collections WHERE collectionKey='Posts'
    0.0005 get_collection(): ">[nil] SELECT * FROM ( SELECT idx.itemID, ci.collectionID, ci.itemJSON, idx2.indexValue as sortval FROM perch2_collection_index idx JOIN perch2_collection_items ci ON idx.itemID=ci.itemID AND idx.itemRev=ci.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_revisions cr ON idx.itemID=cr.itemID AND idx.itemRev=cr.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='post_date' WHERE (idx.collectionID=3) AND ((idx.indexKey='post_date' AND idx.indexValue <= '2020-03-08 09:35:47') OR (idx.indexKey='post_date' AND idx.indexValue=2020)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, itemJSON, sortval HAVING count(*)=2 ORDER BY sortval DESC
    0.0015 get_collection(): ">Using template: /templates/content/post.html
    0.0004 get_custom(): ">[1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='Contact details' AND (regionPage='/contact' OR regionPage='*')
    0.0004 get_custom(): ">[1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=36 AND idx.itemRev=11)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval, regionID ORDER BY sortval ASC
    0.0015 get_custom(): ">[1] Using template: /templates/content/contact_footer.html
    0.0002 find_all_tag_ids(): ">Using sub-template: /templates/content/_address.html
    0.0001 find_all_tag_ids(): ">Using sub-template: /templates/layouts/svg/twitter.svg
    0.0007 _get_content(): ">[3] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/news' OR regionPage='*' ORDER BY regionPage DESC
    0 get_route(): ">[6] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate, pr.routeOrder, s.settingValue AS siteOffline FROM perch2_pages p LEFT JOIN perch2_page_routes pr ON p.pageID=pr.pageID LEFT JOIN perch2_settings s ON s.settingID='siteOffline' UNION SELECT NULL AS pagePath, pr2.routePattern, pr2.routeRegExp, pr2.templatePath AS pageTemplate, pr2.routeOrder, NULL AS siteOffline FROM perch2_page_routes pr2 WHERE templateID!=0 ORDER BY routeOrder ASC, pagePath ASC
    0.0019 get_route(): ">Matched route: [year:filter_year]
    0.0001 Using master page: /templates/pages/news.php
    0 Page arguments:
    Code
    1. Array
    2. ( [0] => /2020 [filter_year] => 2020 [1] => 2020
    3. )
    0.0067 find_by_path(): ">[1] SELECT * FROM perch2_pages WHERE pagePath='/news' LIMIT 1
    0.0024 template_attribute(): ">Using template: /templates/pages/attributes/default.html
    0.0182 get_navigation(): ">[5] SELECT * FROM perch2_pages WHERE pageNew=0 AND pageHidden=0 AND pageDepth >=0 AND pageDepth<=1 ORDER BY pageTreePosition ASC
    0.0004 get_value(): ">[1] SELECT pageTreePosition FROM perch2_pages WHERE pagePath='/news' LIMIT 1
    0.0002 find_parent_page_ids_by_path(): ">[1] SELECT pageID FROM perch2_pages WHERE pageTreePosition IN ('000-003', '000') ORDER BY pageTreePosition DESC
    0.0002 _template_nav(): ">[5] Using template: /templates/navigation/item.html
    0.0014 get_collection(): ">[1] SELECT collectionID, collectionTemplate FROM perch2_collections WHERE collectionKey='Posts'
    0.0005 get_collection(): ">[nil] SELECT * FROM ( SELECT idx.itemID, ci.collectionID, ci.itemJSON, idx2.indexValue as sortval FROM perch2_collection_index idx JOIN perch2_collection_items ci ON idx.itemID=ci.itemID AND idx.itemRev=ci.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_revisions cr ON idx.itemID=cr.itemID AND idx.itemRev=cr.itemRev AND idx.collectionID=ci.collectionID JOIN perch2_collection_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='post_date' WHERE (idx.collectionID=3) AND ((idx.indexKey='post_date' AND idx.indexValue <= '2020-03-08 09:35:47') OR (idx.indexKey='post_date' AND idx.indexValue=2020)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, itemJSON, sortval HAVING count(*)=2 ORDER BY sortval DESC
    0.0015 get_collection(): ">Using template: /templates/content/post.html
    0.0004 get_custom(): ">[1] SELECT regionID, regionTemplate, regionPage, regionRev AS rev FROM perch2_content_regions WHERE regionKey='Contact details' AND (regionPage='/contact' OR regionPage='*')
    0.0004 get_custom(): ">[1] SELECT * FROM ( SELECT idx.itemID, c.regionID, idx.pageID, c.itemJSON, idx2.indexValue as sortval FROM perch2_content_index idx JOIN perch2_content_items c ON idx.itemID=c.itemID AND idx.itemRev=c.itemRev AND idx.regionID=c.regionID JOIN perch2_content_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='_order' WHERE ((idx.regionID=36 AND idx.itemRev=11)) AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev ) as tbl GROUP BY itemID, pageID, itemJSON, sortval, regionID ORDER BY sortval ASC
    0.0015 get_custom(): ">[1] Using template: /templates/content/contact_footer.html
    0.0002 find_all_tag_ids(): ">Using sub-template: /templates/content/_address.html
    0.0001 find_all_tag_ids(): ">Using sub-template: /templates/layouts/svg/twitter.svg
    0.0007 _get_content(): ">[3] SELECT regionKey, regionHTML FROM perch2_content_regions WHERE regionPage='/news' OR regionPage='*' ORDER BY regionPage DESC
    0.0042 Request time: 0.0939
    0 Process time: 0.0827
    0 Memory: 3.5229



    And here's the diagnostics:


    • Perch Runway: 3.1.5, PHP: 7.2.22, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $, with PDO
    • Server OS: Darwin, apache2handler
    • Installed apps: content (3.1.5), assets (3.1.5), categories (3.1.5), perch_forms (1.12), perch_backup
    • App runtimes: <?php $apps_list = [ 'perch_forms', ];
    • PERCH_LOGINPATH: /cms
    • PERCH_PATH: /Users/martin/Sites/grahamaccountants.com/cms
    • PERCH_CORE: /Users/martin/Sites/grahamaccountants.com/cms/core
    • PERCH_RESFILEPATH: /Users/martin/Sites/grahamaccountants.com/cms/resources
    • Image manipulation: GD
    • PHP limits: Max upload 32M, Max POST 8M, Memory: 128M, Total max file upload: 8M
    • F1: 3b606135b33e6a102526838f4152a807
    • Resource folder writeable: Yes
    • HTTP_HOST: grahamaccountants.local
    • DOCUMENT_ROOT: /Users/martin/Sites/grahamaccountants.com
    • REQUEST_URI: /cms/core/settings/diagnostics/
    • SCRIPT_NAME: /cms/core/settings/diagnostics/index.php
  • drewm

    Approved the thread.
  • Do both the filters need to be matched to pass the test, or just one of them? Try adding 'match' => 'or' between the filters if it's just one.


    Also have you tried the option ''filter-mode' => 'ungrouped', as this can help with sticky multiple filters.

  • 'filter-mode' => 'ungrouped' does nothing for collections.


    For reference this is your original code:



    It seems your are passing a year with perch_get('filter_year'). You can't just pass the year (e.g. 2018) to filter by a date field. You need to provide a full date like 2018-01-01 00:00:00. The Blog app comes with an archive page which you can use as a reference for date filtering.


    You need to rewrite the filter as a date range e.g. from 1 January 2018 to 12 December 2018:




    In your case it looks like you also need to check whether $date_to is greater than the current date:


    PHP
    1. $year = perch_get('filter_year');
    2. $now = date('Y-m-d H:i:s');
    3. $date_from = $year . '-01-01 00:00:00';
    4. $date_to = $year . '-12-31 23:59:59';
    5. if(strtotime($date_to) > strtotime($now)) {
    6. $date_to = $now;
    7. }
  • Proposed Functionality. Handling of API requests involving multiple filter values should be consistent regardless of the fields involved, such that the logical OR of matching records will be returned. At the very least, multiple filters involving the same field should always work to return all matches.