Multiple Specifications as Filters

[itn-ms]

Hey all,

I have multiple Specification Groups which are used for filtering. However I have some problems with this at the moment:
When I set the Filter Method for Specifications to "All", the SQL query doesn't seem to get built correctly. Only the last filter in the URL is used the rest ignored.
In my case I have activated the filters "glass and metal" for Specification Group material and "gold" for Specification Group color. However in the query the second material isn't shown at all - while the first is inside the INNER JOIN instead of the WHERE clause. The only specification in the WHERE is "gold" which is also the only thing that gets filtered.
So for example my filter-URL looks like this: exampleshop.com/index.php/phoca/items?s[material]=metall,glas&s[farbe]=gold
And the resulting SQL looks like this:

Code: Select all

 ...) 
  AND a.id IN (
    SELECT 
      s2x0.product_id 
    FROM 
      hhp59_phocacart_specifications AS s2x0 
      INNER JOIN hhp59_phocacart_specifications AS s2x1 ON s2x1.product_id = s2x1.product_id 
      AND s2x1.alias = 'material' 
      AND s2x1.alias_value = "glas" 
    WHERE 
      s2x0.alias = 'farbe' 
      AND s2x0.alias_value = "gold"
  ) 
GROUP BY 
...

Also I would need the following filtering behaviour: It should be like ANY inside each Specification Group, and like AND between each Group. So if I filter for material glass and metal and color gold, I want everything which is made out of metal OR glass AND has the color gold. Is that possible at the moment with multiple Specification Groups and if so what do I need to config?

Thanks in advance for your input!

Marco

[Jan]

Hi, which version of Phoca Cart do you use?

In options you can define if any or all will be used when searching:



Jan

[itn-ms]

Hi Jan,
thanks for your answer.

Sorry I forgot to mention it: I use Phoca Cart 5.0.2 on Joomla 5.3.0.

I defined in the Filter Options "All" for the Specifications, then the filters don't seem to work properly - like I described. The Search Matching Option doesn't change the results.

Kind regards,
Marco

[Jan]

Don't you get any different when switching between any, all, exact?

Jan

[itn-ms]

Hi Jan,

the filters work when I switch them to any just like they should: If I have various filters active more products are added to the results (everything that has the filtered specification). So in my example if I turn on the filters glass and metal together with the color gold, I get every product out of all categories which is either gold or out of glass or out of metal.

However I would need the following behaviour: If I tick for material glass and metal and for color gold, the filtered products should be out of glass or metal and should be gold. So instead of adding new products to the results like with any, my results should be less products the more filters I turn on. So I switched from Any to All.
Regardless of the glass Or metal filtering behaviour inside the specification group, the filters don't seem to work properly then since only the last active filter in the URL gets filtered at all - so I just get every product that's gold. I believe this has to be a bug.

Kind regards,
Marco

[Jan]

Hi, maybe it will be great if you can install e.g. n3t debug plugin and see the SQL which is run when the specifications are selected, to see if there is a problem in the SQL or if there is some limitation which cannot be changed.

Jan

[itn-ms]

Hi Jan,
I have the n3t debug plugin and the SQL from my initial post is copy pasted from there
I thought it was sketchy that only the last specification is in the WHERE clause of the query the others were added to the INNER JOIN with AND:

Code: Select all

 ...) 
  AND a.id IN (
    SELECT 
      s2x0.product_id 
    FROM 
      hhp59_phocacart_specifications AS s2x0 
      INNER JOIN hhp59_phocacart_specifications AS s2x1 ON s2x1.product_id = s2x1.product_id 
      AND s2x1.alias = 'material' 
      AND s2x1.alias_value = "glas" 
    WHERE 
      s2x0.alias = 'farbe' 
      AND s2x0.alias_value = "gold"
  ) 
GROUP BY 
...

Also the material metall isn't even mentioned in the query although I set the filter active together with glas.

Kind regards,
Marco

[Jan]

Hmmm, I am testing now specifications on test servers, trying to set similar specifications and see no problem there.

[itn-ms]

Hi Jan,
thanks for your efforts!

To be sure I also setup another instance and added some specification groups to the demo data.
In my test I first filtered for specifications packaging = eco and color = blue:
https://imgur.com/a/MBuLUhz
In this test packaging is ignored and the results are only filtered for color blue.

Then I added flavour = apple to the filters:
https://imgur.com/a/ytNbYjX
Here the color and the packaging filters are ignored and the products are only filtered for flavour apple.
It's the last part of the URL only that gets recognized.

When I then add another flavour from an already active group to the filters for example flavour = mango, the filtered results don't change:
https://imgur.com/a/yBRqMSx
But the SQL changes and mango gets added to the INNER JOIN instead of the WHERE.

In the options I set the filter option for specifications to all.
The test was on PhocaCart v5.0.3 and with the v5.0.0 Filter Module.

Kind regards,
Marco

[Jan]

Hi, testing now, the first case, I get normally two products filters - by color and by packaging:



The filtering is set to default:



Jan