Source Source - 3 months ago 18
MySQL Question

MYSQL query that selects from filters

I am trying to add a product filter feature. Each product could have an arbitrary number of filters.

I have set up my table like so:

row_id products_id filters_id
1 1 1
2 2 2
3 3 3
4 4 3
5 1 3


So the table has a list of product_ids and corresponding filter_ids. As this seemed like such a simple table, I assumed that the script would be easy. But I am really struggling to get my head around it.

There query I got to, was:

SELECT p.products_id
, p.products_name
, p.products_short_desc
, p.products_price
, p.products_url
, p.products_image
, p.products_short_desc
, p.contact_pricing
, GROUP_CONCAT(ptc.categories_id) category_id
FROM products p
LEFT
JOIN prod_to_cat ptc
ON ptc.products_id = p.products_id
AND ptc.categories_id IN (3)
WHERE p.products_status = 1
GROUP
BY p.products_id
HAVING COUNT(DISTINCT ptc.categories_id) = 1
ORDER
BY p.products_price


As an example, if just one filter was selected, and it was id 3. This failed to work properly, even with much tweaking. It also seems fair to complex, for something I thought would have been so simple.

So essentially, I am trying to work out how, with this table, can I select all the products, that have all the match filters_id's?

So if if filter 1 and 3 were selected, I would want to yield the results of all products that match, i.e. are in the table with their product id and corresponding filter_ids. In this example, it would just return products_id 1, if just filters id 3 was selected it would return product 3,4 and 1. How is this achieved?

Answer

It seems fairly straightforward to me:

DROP TABLE IF EXISTS product_filters;

CREATE TABLE product_filters
(product_id INT NOT NULL
,filter_id INT NOT NULL
,PRIMARY KEY(product_id,filter_id)
);

INSERT INTO product_filters VALUES
(1,1),
(2,2),
(3,3),
(4,3),
(1,3);

SELECT product_id 
  FROM product_filters 
 WHERE filter_id IN(1,3) 
 GROUP 
    BY product_id 
HAVING COUNT(*) = 2;
+------------+
| product_id |
+------------+
|          1 |
+------------+

SELECT product_id 
  FROM product_filters 
 WHERE filter_id IN(3) 
 GROUP 
    BY product_id 
HAVING COUNT(*) = 1;
+------------+
| product_id |
+------------+
|          1 |
|          3 |
|          4 |
+------------+