We have a basic web application that serves up some data from a MySql DB. I am trying to add a simple filtering mechanism that will allow users to type some basic filtering expressions into a search box to help filter results. I already have some code to parse and separate the expressions, but now I am trying to translate that into a SQL statement.
It's fairly simple to add additional statements to the WHERE clause to filter on columns that are part of the table, but the some properties of the table are mapped via many-to-many relationship. This is where I am having issue structuring the query. Here is an example of the table structure and what a filter might loo
states
| id | name | abbreviation |
| 1 | Florida | FL |
| 2 | Arizona | AZ |
descriptions
| id | description |
| 1 | hot |
| 2 | humid |
| 3 | dry |
states_descriptions
| state_id | description_id |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
So say we have an example filter a user wants to apply where they want to see all states that are 'humid or hot' and another that is 'humid and hot'. In the 'humid or hot' I would expect my results to be Florida and Arizona. In the 'humid and hot' I would expect to only get back Florida. I have a couple of approaches to building the query, but none seem to be the correct approach.
Approach 1: Using a subquery to filter. Example:
SELECT *
FROM states
WHERE id in (
SELECT s.id
FROM states AS s
JOIN states_descriptions AS sd ON sd.state_id = s.id
JOIN descriptions AS d ON sd.description_id = d.id
WHERE <The confusing bit>)
My issue with this approach is the where clause in the subquery. If I wanted to apply the 'or' filter. It would be:
WHERE d.description = 'hot' OR d.description = 'humid'
but the 'and' filter won't work this way. I can write a query where the 'and' filter would work, but remember from above that I am trying to build these queries dynamically from user statements. This is a simplified example, so the actual filtering can be more complex and have multiple statements.
Approach 2: Using GROUP_CONCAT
I have some seen some attempts to do this using GROUP_CONCAT statements to aggregate the properties from a join into a single column, and searching through that like a string.
Example:
SELECT s.*, GROUP_CONCAT(d.description) as desc
FROM states AS s
JOIN states_descriptions AS sd ON sd.state_id = s.id
JOIN descriptions AS d ON sd.description_id = d.id
GROUP BY s.id
HAVING <some statement here>
This works but it seems inefficient. My actual tables have a lot of data, so I have to be a bit more mindful of performance. I could apply both filters easily with HAVING statements like:
HAVING desc like '%hot%' and desc like '%humid%'
But that is awful for performance.
I build the queries using a query builder with a fluent interface, so its fairly easy to append statements, but I am trying to avoid having multiple queries depending on what the user specifies. I am open to a completely different approach, but the table structure is something I cannot change.
