Forum
Problem with user-filtered list in Perch 3
Hi,
on a page I have a user-filtered list (like explained at https://docs.grabaperch.com/perch/content/functions/user-filtered-lists/ ), so the user can filter the content by date, duration, age, price, etc.
On the main site (Perch Runway 2) all kind of filtering works well and the content is shown as expected. But on the staging site (Perch Runway 3) I get a blank page if I have the case that the filter contains dates/numbers and another filter, eg: "xxx?date=2017-06-03%2C2017-06-18&dauer=eintagig&altersgruppen=&price=" : with Perch 2 everything is filtered as expected, but on Perch 3 I get a blank page
Does anyone knows why this happens?
The filtering form:
<perch:form id="filter" method="get">
<div>
<perch:label for="date">Zeitraum</perch:label>
<perch:input type="select" id="date" options="Aktuelle Termine|,Alle Termine|2017-01-01\,2017-12-31,Pfingstferien|2017-06-03\,2017-06-18,Sommerferien|2017-07-26\,2017-09-10,Herbstferien|2017-10-28\,2017-11-05,Mai|2017-05-01\,2017-05-31,Juni|2017-06-01\,2017-06-30,Juli|2017-07-01\,2017-07-31,1.-15. August|2017-08-01\,2017-08-15,16.-31. August|2017-08-16\,2017-08-31,September|2017-09-01\,2017-09-30,Oktober|2017-10-01\,2017-10-31,November|2017-11-01\,2017-11-30" />
</div>
<div>
<perch:label for="dauer">Dauer</perch:label>
<perch:input type="select" id="dauer" options="Alle Termine|,Eintägig|eintagig,Mehrtägig|mehrtagig" />
</div>
<div>
<perch:label for="altersgruppen">Alter</perch:label>
<perch:input type="select" id="altersgruppen" options="Alle Termine|,bis 5 Jahre|bis-5-jahre,6–10 Jahre|6-10-jahre,11–14 Jahre|11-14-jahre,ab 15 Jahre|ab-15-jahre" />
</div>
<div>
<perch:label for="price">Kosten</perch:label>
<perch:input type="select" id="price" options="Alle Termine|,nur Kostenlos|0.00" />
</div>
<div class="filter-checkbox">
<label>Nur mit Hinweis:</label>
<perch:input type="checkbox" id="disabled" value="yes" />
<perch:label for="disabled"><img src="/assets/img/disabled.png" width="35"></perch:label>
<perch:input type="checkbox" id="diplom" value="yes" />
<perch:label for="diplom"><img src="/assets/img/diplom.png" width="35"></perch:label>
</div>
<div><perch:input class="button" type="submit" value="Anzeigen" /></div>
</perch:form>
and the page-template:
$filters = array();
if (perch_get('date')) {
$filters[] = array(
'filter' => 'date',
'match' => 'eqbetween',
'value' => perch_get('date'),
);
}
if (perch_get('dauer')) {
$filters[] = array(
'filter' => 'dauer',
'match' => 'eq',
'value' => perch_get('dauer'),
);
}
if (perch_get('price')) {
$filters[] = array(
'filter' => 'price',
'match' => 'eq',
'value' => perch_get('price'),
);
}
if (perch_get('disabled')) {
$filters[] = array(
'filter' => 'disabled',
'match' => 'eq',
'value' => perch_get('disabled'),
);
}
if (perch_get('diplom')) {
$filters[] = array(
'filter' => 'diplom',
'match' => 'eq',
'value' => perch_get('diplom'),
);
}
if(perch_get('altersgruppen') != 'altersgruppen') { $varAlter = perch_get('altersgruppen'); } else { $varAlter = NULL; }
if ($varAlter) { $varCombinedCategories = array('altersgruppen/'.$varAlter); } else { $varCombinedCategories = ''; }
if (!count($filters)) {
$filters[] = array(
'filter' => 'date',
'match' => 'gte',
'value' => date('Y-m-d'),
);
}
PerchSystem::set_var('today', date('Y-m-d'));
perch_shop_products(array(
'sort' => 'sku',
'sort-order' => 'ASC',
'sort-type' => 'numeric',
'filter' => $filters,
'category' => $varCombinedCategories,
'paginate' => true,
'count' => 12,
'page-links' => true,
));
Diagnostic-Report for the Perch2 site:
Perch Runway: 2.8.34, PHP: 7.0.16, MySQL: 5.5.52, with PDO
Server OS: SunOS, cgi-fcgi
Installed apps: content (2.8.34), assets (2.8.34), categories (2.8.34), perch_forms (1.8.3), perch_shop_orders (1.0.12), perch_shop_products (1.0.12), perch_shop (1.0.12), perch_members (1.5)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_forms', 'perch_members', 'perch_shop', );
PERCH_LOGINPATH: /perch
PERCH_PATH: /mnt/web023/d0/78/5477578/htdocs/ferienspass/perch
PERCH_CORE: /mnt/web023/d0/78/5477578/htdocs/ferienspass/perch/core
PERCH_RESFILEPATH: /mnt/web023/d0/78/5477578/htdocs/ferienspass/perch/resources
Image manipulation: GD
PHP limits: Max upload 64M, Max POST 76M, Memory: 256M, Total max file upload: 64M
F1: 6a33f95eca3667f9e0c39bf5ca2980fe
Resource folder writeable: Yes
HTTP_HOST: ferienspass-ludwigsburg.de
DOCUMENT_ROOT: /home/strato/http/premium/rid/75/78/5477578/htdocs
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
REQUEST_URI: /perch/core/settings/diagnostics/
and for the Perch3 site:
Perch Runway: 3.0, PHP: 7.0.16, MySQL: 5.5.52, with PDO
Server OS: SunOS, cgi-fcgi
Installed apps: content (3.0), assets (3.0), categories (3.0), perch_forms (1.9), perch_shop_orders (1.1), perch_shop_products (1.1), perch_shop (1.1), perch_members (1.6)
App runtimes: <?php $apps_list = [ 'perch_forms', 'perch_members', 'perch_shop', ];
PERCH_LOGINPATH: /perch
PERCH_PATH: /mnt/web023/d0/78/5477578/htdocs/staging/ferienspass/perch
PERCH_CORE: /mnt/web023/d0/78/5477578/htdocs/staging/ferienspass/perch/core
PERCH_RESFILEPATH: /mnt/web023/d0/78/5477578/htdocs/staging/ferienspass/perch/resources
Image manipulation: GD
PHP limits: Max upload 64M, Max POST 76M, Memory: 256M, Total max file upload: 64M
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
Resource folder writeable: Yes
HTTP_HOST: ferienspass.staging.r-grafik.de
DOCUMENT_ROOT: /home/strato/http/premium/rid/75/78/5477578/htdocs
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
REQUEST_URI: /perch/core/settings/diagnostics/
Have you tried turning on debug to see what is happening? In particular look for the mysql query relating to perch_shop_products, which you can copy and run yourself in phpmyadmin to see what is going on, and whether there are any errors.
Thanks for the advice. I have checked the debug and had also run the mysql query in phpmyadmin. The mysql query looks nearly the same in Perch 2 and Perch 3 with one small difference:
Perch2: ... WHERE 1=1 AND ((idx.indexKey='date' AND (idx.indexValue >= '2017-06-03' AND idx.indexValue <= '2017-06-18')) OR (idx.indexKey='dauer' AND idx.indexValue='eintagig')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval HAVING count(*)=2 ORDER BY sortval * 1 ASC LIMIT 0, 12
Perch3: ... WHERE 1=1 AND ((idx.indexKey='date' AND (idx.indexValue >= '2017-06-03' AND idx.indexValue <= '2017-06-18')) OR (idx.indexKey='dauer' AND idx.indexValue='eintagig')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, productID) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval HAVING count(*)=2 ORDER BY sortval * 1 ASC LIMIT 0, 12
The difference in Perch3 is that "GROUP BY idx.itemID, idx2.indexValue, productID" is added after "idx.itemKey=idx2.itemKey". Could this cause the problem?
In myphpadmin I get with the Perch2-query in both databases the correct results, and with the Perch3-query no results.
Does anyone knows a solution for that problem?
Hi Rene, we're looking into this. The group by was added for performance with large datasets. It shouldn't be affecting the result, but if it is we'll see if we can add the option to turn it off again, once we understand why.
There's a new filter mode option you can use:
Does that help?
Hi Drew,
that’s it :). Now the filter works as expected.
Thanks for your great support