MYSQL - Querying a table with optional filters and default value


  • QA Engineer

    I have a table that looks like this:

    ID KEY FILTER_NAME FILTER_VALUE VALUE
    1 a country USA value1
    2 a inRockBand true value2
    3 a value3
    4 b city MADRID value4
    5 b isLawer true value5
    6 b value6

    I would like to create a query that can receive generic filter (optional) parameters and fetches a single row per key accordingly (when optional filters don't match - the query will return the row with null filter name\value as these are the default rows)

    for example:

    select * from table where filterName = 'country' and filter_value = 'SPAIN'
    

    will return:

    ID KEY FILTER_NAME FILTER_VALUE VALUE
    3 a value3
    6 b value6

    while

    select * from table where filterName = 'inRockBand' and filter_value = 'true'
    

    will return:

    ID KEY FILTER_NAME FILTER_VALUE VALUE
    2 a inRockBand true value2
    6 b value6

    if several filters matches several keys we should take any one key



  • SELECT COALESCE(t1.ID, t2.ID) ID,
           t0.`KEY`,
           COALESCE(t1.FILTER_NAME, t2.FILTER_NAME) FILTER_NAME,
           COALESCE(t1.FILTER_VALUE, t2.FILTER_VALUE) FILTER_VALUE,
           COALESCE(t1.VALUE, t2.VALUE) VALUE
    FROM ( SELECT @filterName FILTER_NAME,
                  @filter_value FILTER_VALUE ) criteria
    CROSS JOIN ( SELECT DISTINCT `KEY`
                 FROM test ) t0
    LEFT JOIN test t1 ON criteria.FILTER_NAME = t1.FILTER_NAME
                     AND criteria.FILTER_VALUE = t1.FILTER_VALUE
                     AND t0.`key` = t1.`key`
    LEFT JOIN test t2 ON '' = t2.FILTER_NAME
                     AND '' = t2.FILTER_VALUE
                     AND t0.`key` = t2.`key`
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=feceb7a404ddd6a78324664c51c3c310


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2