CODEWITHSUNDEEP
mysqlsqle-commerce
Richard Dev
Richard Dev

Reputation: 1170

MySQL Filtering Attributes

I am trying to create a better way of filtering items on a website. The actual filtering is working great but displaying the available options has stumped me.

I am using this sql query to get all the options for a specific category.

SELECT 
atr1.`type` , atr1.`value`
FROM 
`index-shop-filters` AS atr1 
JOIN 
`index-shop-filters` as atr2 ON atr1.`item` = atr2.`item`  
WHERE 
( atr2.`type` = 'sys-category' AND atr2.`value` = '1828' ) 
GROUP BY 
atr1.`type`, atr1.`value`

But when I add a selected filter in the hope to get the remaining filters available. It doesn't give me the remaining filters. Instead it ignores the second OR statement. 

SELECT 
atr1.`type` , atr1.`value`
FROM 
`index-shop-filters` AS atr1 
JOIN 
`index-shop-filters` as atr2 ON atr1.`item` = atr2.`item`  
WHERE 
( atr2.`type` = 'sys-category' AND atr2.`value` = '1828' ) 
OR ( atr2.`type` = 'Manufacturer' AND atr2.`value` = 'Sony' )
GROUP BY 
atr1.`type`, atr1.`value`

I tried adding the HAVING COUNT(*) = 2 but that doesn't get the correct results.

The data in the index-shop-filters is like this.

item,type,value

the types are sys-category, manufacturer, size, color, etc.

When they select the first option (like sys-category) it will then display the available options. If they then select manufacturer (like sony) it will then display the available options that the items are sony, and in the category.

Upvotes: 1

Views: 1195

Answers (1)

ean5533
ean5533

Reputation: 8994

Ok, I think I finally understand what you're trying to do: you aren't trying to get a list of items, you're trying to get a list of item filters. Sorry, I should have picked up on that sooner.

Anyway, now I understand the problem, but I don't have a great answer. What you're trying to do is fairly complicated, and it can't be done with just one join (as far as I know). I can only think of two ways to do this: with multiple subqueries or with multiple joins. Both of these solutions are complicated and do not scale well, but it's all I can think of.

Here is one possible solution, using subqueries, that I do not recommend:

SELECT item, `type`, `value`
FROM `index-shop-filters` AS f
WHERE f.item IN (SELECT item FROM `index-shop-filters` WHERE `type` = 'sys-category' AND `value` = '1828')
AND f.item IN (SELECT item FROM `index-shop-filters` WHERE `type` = 'Manufacturer' AND `value` = 'Sony')

Here is a solution, using joins, that is better but still not great:

SELECT item, `type`, `value`
FROM `index-shop-filters` AS f
JOIN `index-shop-filters` AS f2 ON f.item = f2.item AND f2.`type` = 'sys-category' AND f2.`value` = '1828'
JOIN `index-shop-filters` AS f3 ON f.item = f3.item AND f3.`type` = 'Manufacturer' AND f3.`value` = 'Sony'

And that's all I've got. Both of those solutions should work, but they won't perform well. Hopefully someone else can come up with a clever, scalable answer.

Upvotes: 1

Related Questions