skip to Main Content

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


  1. I think you want aggregation, because the values are in different rows:

    SELECT something
    FROM tableXY
    GROUP BY something 
    HAVING SUM( value BETWEEN 100 AND 200 AND key ='price' ) > 0 AND
           SUM( value BETWEEN 2 AND 5 AND key = 'rooms' ) > 0 AND
           SUM( value BETWEEN 40 AND 80 AND key = 'sqft' ) > 0 AND
           ......and so on.
    
    Login or Signup to reply.
  2. Seems like you need a logical or operation between each pair of conditions on value and key:

    SELECT *
    FROM   tableXY
    WHERE  (value BETWEEN 100 AND 200 AND key ='price' ) OR 
           (value BETWEEN   2 AND   5 AND key = 'rooms') OR
           (value BETWEEN  40 AND  80 AND key = 'sqft' ) -- etc...
    

    Note: Since and has a higher precedence than or the parenthesis aren’t strictly needed, but they do make the query easier to read.

    Login or Signup to reply.
  3. Solution 1: Use or between conditons for different key values.

    SELECT * FROM 'tableXY' WHERE (value BETWEEN 100 AND 200 AND key ='price') 
    OR (value BETWEEN 2 AND 5 AND key = 'rooms')
    OR (value BETWEEN 40 AND 80 AND key = 'sqft')......and so on.
    

    Solution 2: You need to use case when

    SELECT * FROM 'tableXY' 
    WHERE 1 = (
     case 
      when key ='price' and value BETWEEN 100 AND 200 
         then 1
      when key ='rooms' and value BETWEEN 2 AND 5
         then 1
    .....
      else 0
     end
    ) 
    

    Here is why your initial clause didn’t find records. Your initial approach using AND between condition sets basically selects
    WHERE ... value BETWEEN 2 AND 5 AND... value BETWEEN 40 AND 80
    Obviously no result

    Login or Signup to reply.
  4. If you used union in a similar way to:

    QueryWithRoomCostFilter UNION  QueryWithRoomSizeFilter
    

    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.

    SELECT * 
    FROM table_name
    WHERE (price BETWEEN 100 AND 200)
    AND (rooms BETWEEN 2 AND 5)
    AND (sqft BETWEEN 40 AND 80)
    
    Login or Signup to reply.
  5. 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.

    CREATE TABLE tableXY (
        `price` DOUBLE,
        `room` INT
        `sqlft` INT,
        ... OTHER indicators ...
    );
    
    SELECT * 
    FROM tableXY 
    WHERE `price` BETWEEN 100 AND 200
      AND `rooms` BETWEEN 2 AND 5
      AND `sqft` BETWEEN 40 AND 80
    

    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.).

    CREATE TABLE tableXY (
        `key` VARCHAR(10),
        `value` INT
    );
    
    SELECT sub.* 
    FROM 
       (SELECT CASE WHEN `key` = 'name' THEN `key` ELSE NULL END AS `name`,
               SUM(CASE 
                       WHEN `key` = 'price'
                       THEN `value`
                       ELSE NULL
                   END) AS price,
               SUM(CASE 
                       WHEN `key` = 'rooms' 
                       THEN `value`
                       ELSE NULL
                   END) AS rooms,
               SUM(CASE 
                       WHEN `key` = 'sqlft' 
                       THEN `value
                       ELSE NULL
                   END) AS sqft,
    
               -- ...other values...
    
         FROM tableXY
    
         GROUP BY CASE WHEN `key` = 'name' THEN `key` ELSE NULL END
      ) AS sub
    
    WHERE sub.price BETWEEN 100 AND 200
      AND sub.rooms BETWEEN 2 AND 5
      AND sub.sqft BETWEEN 40 AND 80
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search