MYSQL query that selects from filters

Question

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?

So if filter 1 and 3 were selected the output would display row id 1,3,4,5? — Jeff
– Jeff, Commented Aug 28, 2016 at 21:58
No, its not an OR, its an AND. so if filters 1 & 3 were selected only products_id 1 would be displayed. E.g. you want a red dress, size Medium, not red dresses or medium dresses. — Source
– Source, Commented Aug 28, 2016 at 22:02
I'm not sure i follow, is the filter applied to more than one column (property) of the object? — Jeff
– Jeff, Commented Aug 28, 2016 at 22:06
So what's wrong with your query, except of using wrong table (prod_to_cat) and/or wrong column (ptc.categories_id IN (3))? — Paul Spiegel
– Paul Spiegel, Commented Aug 28, 2016 at 22:07

Strawberry · Accepted Answer · 2016-08-28 23:47:24Z

2

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 |
+------------+

answered Aug 28, 2016 at 23:47

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Source Over a year ago

Thanks, what would be the full query so I can test it?

Strawberry Over a year ago

Based on the data set provided, THAT would be the full query. If you want a fuller answer, ask a fuller question! My answer should serve as an example.

Source Over a year ago

OH! So it works by showing rows that have a count the same as the number of filters? That's really clever.

Source Over a year ago

So I just need to work out how to join the products table as well, and it should be rocking

Collectives™ on Stack Overflow

MYSQL query that selects from filters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related