Forum

Thread tagged as: Error, Runway

Bug in Perch_Collection Filter

Hi Drew,

I think i've come across a bug in Perch_Collection (which probably affects Perch_content_custom too, although I haven't had a chance to check!). When filtering on multiple fields, where one of the fields is a repeater, it appears that incorrect results are returned.

I managed to locate and fix the cause of the issue which is I believe is due to the way that the results are counted in PerchContent_Runway.class.php.

Example Perch_Collection call in master template

perch_collection('Events', [
    'template'  => 'events/event-jumbotron.html',
    'count'     => 1,
    'filter'    => array(
        array(
            'filter'        =>'event-schedule.event-date',
            'match'     =>'gte',
            'value'     => date("Y-m-d H:i:s")),
        array(
            'filter'=>'event-cancelled',
            'match'     =>'neq',
            'value'     => 'yes')),
    'sort'      => 'event-schedule.event-date',
    'order' => 'ASC'
]);

Example Template

<perch:content id="event-cancelled" type="checkbox" label="Event Cancelled" value="yes"/>

<perch:repeater id="event-schedule" label="Event Schedule"">
    <perch:content id="event-date" type="date" time="true" label="Date/Time" format="d F Y H:i" />
</perch:repeater>

Fixes in PerchContent_Runway.class.php

Line 99

$sortval = ' idx.indexKey, idx2.indexValue as sortval ';

Line 289

$sql .= ' AND idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev
                      group by itemID, indexKey
                    ) as tbl GROUP BY itemID ';

Does this look like a bug to you too, or have I done something stupid!? Give me a shout if you need any more details.

Alex
Alex Lewer

Alex Lewer 3 points

  • 6 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

Which version are you running?
Alex Lewer

Alex Lewer 3 points

6 years ago

Hi Drew,

Diagnostic report below:

Perch Runway: 2.7.10, PHP: 5.6.2, MySQL: 5.5.38, with PDO
Server OS: Darwin, apache2handler
Installed apps: content (2.7.10), assets (2.7.10), categories (2.7.10), perch_blog (4.5.4), collection_1 (2.7.10), perch_forms (1.8.2), perch_mailchimp (2.0.1), perch_members (1.1), perch_twitter (3.5.1)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_blog', 'perch_twitter', 'perch_members' );
PERCH_LOGINPATH: /cms
PERCH_PATH: /Users/Alex/Dropbox/Web Projects/cheltplayhouse.org.uk/cms
PERCH_CORE: /Users/Alex/Dropbox/Web Projects/cheltplayhouse.org.uk/cms/core
PERCH_RESFILEPATH: /Users/Alex/Dropbox/Web Projects/cheltplayhouse.org.uk/cms/resources
Image manipulation: GD
PHP limits: Max upload 32M, Max POST 32M, Memory: 128M, Total max file upload: 32M
Resource folder writeable: Yes
HTTP_HOST: cheltplayhouse.dev
DOCUMENT_ROOT: /Users/Alex/Dropbox/Web Projects/cheltplayhouse.org.uk
REQUEST_URI: /cms/core/settings/diagnostics/
SCRIPT_NAME: /cms/core/settings/diagnostics/index.php
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

I guess I don't follow why you'd want to sort by the field ID.
Alex Lewer

Alex Lewer 3 points

6 years ago

I don't think the sort is causing an issue (it's sorting on the value of the repeater, not the ID - isn't it?!). It's the filter that is causing the problem.

For example, if multiple values inside the repeater match the filter, the filter isn't returning that collection as a match because the SQL query is expecting the number of results to equal the number of filters.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

That's right - the number of results has to match the number of filters for any AND filters to work.
Alex Lewer

Alex Lewer 3 points

6 years ago

Thanks Drew - I'm aware my situation is probably an edge-case.

I think the changes in PerchContent_Runway.class.php that i've made above mitigate that limitation though, so might be worth looking into in a future release as I'm sure someone else will try to do something similar.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

I need to fully understand what's wrong with the results you're getting in order to be able to fix your edge case without breaking everyone else's sites.

How are the results you're getting incorrect?
Alex Lewer

Alex Lewer 3 points

6 years ago

Sure!

So for the example template above, add two event-date items to the event-schedule repeater that are greater than today's date.

Based on the filter, the collection item should match the filter because event-cancelled doesn't equal yes, and there is one or more event-schedule.event-date that are greater than or equal to date("Y-m-d H:i:s").

However, because there is more than one event-schedule.event-date repeater items that match, plus the event-cancelled match, the number of results doesn't equal the number of filters and therefore the collection item isn't returned.
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

Ok, thanks, let me see what I can do!