Skip to content
Advertisement

SQL Query with two different filters

I have a page which shows all cars from DB. I have two filters , both are multiple select filter. For example

filter 1 – Color

Red , green , blue <– All these are checkbox ,can be selected multiple

filter 2 – brand

BMW, Honda , Hyundai <– All these are checkbox ,can be selected multiple

I have done below query

In above query term_id

6 = blue (Color)

2 = green (Color)

3 = BNW (brand)

But with above query I will get all cars which has blue color or green color or BMW brand But how to change in such a way that I get BMW which is blue or green color.

I have 3 tables which handles these categories.

taxonomy table

term_list

term_cars_relationships Table

Advertisement

Answer

You can construct your query with separate joins for each term category, and separate filter conditions for each as well.

Of course, to construct this query, you will need to know the terms’ types before the query.


Also, using GROUP BY cars.id without aggregation is probably a sign of a problem, or just not the right way to get what you want. If you only want the information from the cars table, you should just SELECT DISTINCT cars.*. Using GROUP BY in this manner will end up with results with the data from just one color-brand match for each car.


With the complexity the edit to the original question added, another possibility presents itself….

Note: This final solution does not actually require you to know term taxonomies ahead of time anymore, and does not grow as more taxonomies need supported; so while it is a little less obvious with what it is doing, is probably definitely worth considering.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement