I’m trying to build a filter function with several dependencys but when i add an AND to the SQL query, it seems like no results were found but if i’m searching with the queries before and after the AND, the right results are found.
I try to build a filter function where users can filter between ranges for Example: price: between 100$ and 200$ and squarefeet between 30 and 80.
My approach looks like this:
SELECT * FROM 'tableXY' WHERE value BETWEEN 100 AND 200 AND key ='price'
AND value BETWEEN 2 AND 5 AND key = 'rooms'
AND value BETWEEN 40 AND 80 AND key = 'sqft'......and so on.
The table contains the columns id, foreign_id, key and value. The keys in the rows are price, sqft, rooms, addres etc.
If i filter for a certain price range between price x and price y and rooms between n rooms and m rooms, it should only return results where ervery filter which was set applies to.
No results are found with my approach and if i use an OR instead of an AND, it also shows Results where the other filters dosen’t aplly to.
5
Answers
I think you want aggregation, because the values are in different rows:
Seems like you need a logical
or
operation between each pair of conditions onvalue
andkey
:Note: Since
and
has a higher precedence thanor
the parenthesis aren’t strictly needed, but they do make the query easier to read.Solution 1: Use
or
between conditons for different key values.Solution 2: You need to use
case when
Here is why your initial clause didn’t find records. Your initial approach using
AND
between condition sets basically selectsWHERE ... value BETWEEN 2 AND 5 AND... value BETWEEN 40 AND 80
Obviously no result
If you used union in a similar way to:
Then what happens is it gets the set of tuples that match with your cost filter 100$-200$ and adds them to the result set, then it will evaluate the second query with a filter for room size 40-80 sqft and add those tuples to the result set, effectively combining (taking the union) of both results. In this case, you could see that union is is working as if you had an OR between your conditions.
I don’t use value and between often, but maybe try something along these lines and let us know if it still doesn’t work.
With key-value pair tables as your post indicates, your data is in long format where different unit type of data values and indicators reside in two columns: key and value. Hence, each
AND
condition attempts to select one condition pair of key and value logic and cannot meet all at once unless values like price, rooms, and sqft are stored in their specific columns in a wide format.Long Term Solution
There is a long-debated question on using the entity-attribute value model such as this SO post in relational databases. The long-term solution to effectively filter mutually inclusive conditions is to redesign your table to wide format where values are in their own columns and where querying is much easier with
AND
conditions.Short Term Solution
But the short-term solution for existing long format is to run conditional aggregations to create “pseudo columns” of values: price, rooms, sqft. Add
GROUP BY
for any static attributes (names, locations, time, etc.).