Filtering products with two filters

If you use the function (i.e. https://docs.grabaperch.com/functions/shop/products/perch-shop-products/) you can set the category option and the filter option.

Also, check out the perch_content_custom or perch_collection documentation for further examples - they all work in the same way.

The 'category' => solution works for category filtering.

What about when you want to filter by brand and stock level though? We have thousands of products.

That's for perch collections. How do we move all products or tell perch all my products are a collection?

You use the same principles as described in perch_collection. Where it says perch_collection, replace that with perch_shop_products and you're good to go.

perch_shop_products accepts the same options as perch_collection.

Sorry. It doesn't work. Already tried with arrays and this

    perch_shop_products([
    'count' => 6,
    'filter' => [
    [
    'filter' => 'brand.slug',
    'match' => 'eq',
    'value' => perch_get('brandSlug'),
    ],
    [
    'filter' => 'stock_level',
    'match'=> 'gte',
    'value'=> 1,
    ],
    ]
    ]);

Here's the debug

Debug Message - Perch Runway 3.0.5
[18] SELECT p.pagePath, pr.routePattern, pr.routeRegExp, p.pageTemplate, pr.routeOrder, s.settingValue AS siteOffline FROM perch3_pages p LEFT JOIN perch3_page_routes pr ON p.pageID=pr.pageID LEFT JOIN perch3_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 perch3_page_routes pr2 WHERE templateID!=0 ORDER BY routeOrder ASC, pagePath ASC
Matched route: brands/[slug:brandSlug]
Using master page: \templates\pages\brands.php
Page arguments:
Array
(
    [0] => /brands/kolke
    [brandSlug] => kolke
    [1] => kolke
)
[1] SELECT * FROM perch3_pages WHERE pagePath='/brands' LIMIT 1
[1] SELECT * FROM perch3_shop_cart WHERE cartID=38
[1] SELECT * FROM perch3_shop_cart WHERE cartID=38
[nil] SELECT * FROM perch3_shop_sales WHERE saleFrom<='2017-06-13 13:47:00' AND saleTo>'2017-06-13 13:47:00' AND saleActive=1 AND saleDeleted IS NULL ORDER BY saleOrder ASC
Using template: \templates\pages\attributes\default.html
Using sub-template: \templates\pages\attributes\seo.html
[5] SELECT regionKey, regionHTML FROM perch3_content_regions WHERE regionPage='/brands' OR regionPage='*' ORDER BY regionPage DESC
[5] SELECT * FROM perch3_pages WHERE pageNew=0 AND pageHidden=0 ORDER BY pageTreePosition ASC
[1] SELECT pageTreePosition FROM perch3_pages WHERE pagePath='/brands' LIMIT 1
[1] SELECT pageID FROM perch3_pages WHERE pageTreePosition IN ('000-016', '000') ORDER BY pageTreePosition DESC
[5] Using template: \templates\navigation\item.html
Using template: \templates\search\search-form.html
Using cart from cache.
[40] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
[1] Using template: \templates\shop\cart\cart.html
[1] SELECT pageTreePosition FROM perch3_pages WHERE pagePath='/brands' OR pageSortPath='/brands' LIMIT 1
[nil] SELECT * FROM perch3_pages WHERE pageHidden=0 AND pageNew=0 AND pageTreePosition IN ('000-016', '000') ORDER BY pageTreePosition
[0] Using template: \templates\navigation\breadcrumbs.html
[53] SELECT DISTINCT idx.itemID FROM perch3_shop_index idx JOIN perch3_shop_brands main ON idx.itemID=main.brandID AND idx.itemKey='brandID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
[53] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_brands main ON idx.itemID=main.brandID AND idx.itemKey='brandID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='brandID' AND idx2.indexKey='title' AND idx.itemID IN ('1', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '2', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '3', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '4', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '5', '50', '51', '52', '53', '6', '7', '8', '9') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, brandID ) as tbl WHERE (brandDeleted IS NULL) GROUP BY itemID, sortval ORDER BY sortval ASC
[53] Using template: \templates\shop\brands\list_left.html
[0] SELECT setID FROM perch3_category_sets WHERE setSlug='' LIMIT 1
[36] SELECT main.* FROM perch3_categories main WHERE 1=1 ORDER BY catTitle ASC
[36] Using template: \templates\categories\category.html
[1] SELECT * FROM perch3_gallery_albums WHERE albumSlug= 'brands'
Using template: \templates\gallery\list_image.html
[264] SELECT v.*, i.imageBucket FROM perch3_gallery_image_versions v, perch3_gallery_images i WHERE v.imageID=i.imageID AND i.albumID='2' AND i.imageStatus='active'
[44] SELECT * FROM perch3_gallery_images WHERE albumID = '2' AND imageStatus='active' ORDER BY imageOrder ASC
[nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND ((idx.indexKey='brand.slug' AND idx.indexValue='kolke') OR (idx.indexKey='stock_level' AND idx.indexValue >= 1)) 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 ASC LIMIT 0, 6
Using template: \templates\shop\products\list.html
Rescoping to perch:shop
Using sub-template: \templates\pagination/counter.html
Rescoping to perch:shop
Using sub-template: \templates\pagination/default.html
[53] SELECT DISTINCT idx.itemID FROM perch3_shop_index idx JOIN perch3_shop_brands main ON idx.itemID=main.brandID AND idx.itemKey='brandID' AND ((idx.indexKey='status' AND idx.indexValue='1')) GROUP BY idx.itemID HAVING COUNT(idx.itemID)=1
[6] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_brands main ON idx.itemID=main.brandID AND idx.itemKey='brandID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='brandID' AND idx2.indexKey='title' AND idx.itemID IN ('1', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '2', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '3', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '4', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '5', '50', '51', '52', '53', '6', '7', '8', '9') WHERE 1=1 AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, brandID ) as tbl WHERE (brandDeleted IS NULL) GROUP BY itemID, sortval ORDER BY RAND() LIMIT 0, 6
[6] Using template: \templates\shop\brands\list_footer.html
Using template: \addons\apps\perch_mailchimp\templates\mailchimp\forms\subscribe.html
[1] SELECT * FROM perch3_gallery_albums WHERE albumSlug= 'cards'
Using template: \templates\gallery\list_image.html
[48] SELECT v.*, i.imageBucket FROM perch3_gallery_image_versions v, perch3_gallery_images i WHERE v.imageID=i.imageID AND i.albumID='1' AND i.imageStatus='active'
[8] SELECT * FROM perch3_gallery_images WHERE albumID = '1' AND imageStatus='active' ORDER BY imageOrder ASC
Request time: 2.2563
Process time: 2.2506
Memory: 6.1349

This query seems to be doing what you are asking the filter to do - but it's returning nil:

[nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND ((idx.indexKey='brand.slug' AND idx.indexValue='kolke') OR (idx.indexKey='stock_level' AND idx.indexValue >= 1)) 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 ASC LIMIT 0, 6

Without knowing your data, it's going to be hard to work out why it's not returning any results. If you only use one filter at a time (say brand first, then try again with just stock_level), are results returned?

Tried changing the query to AND instead of OR. Same results. This

SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' AND (idx.indexKey='stock_level' AND idx.indexValue >= 0)

Also returns products with stock_level = 0

See

Drew,

having run this

[nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch3_shop_index idx JOIN perch3_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch3_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND ((idx.indexKey='brand.slug' AND idx.indexValue='kolke') OR (idx.indexKey='stock_level' AND idx.indexValue >= 1)) 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 ASC LIMIT 0, 6

against a database, I am getting zero results when I would expect one returned row.

It's the HAVING count(*)=2 that seems to cause the issue - where is the count number generated from?

Yep. That works. Does that need to be default for some reason?