MYSQL - Querying a table with optional filters and default value

Question

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

Akina · Accepted Answer · 2022-05-27 07:48:48Z

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

mustaccio · Accepted Answer · 2023-02-02 13:22:24Z

0

The query I used looks like so:

select * from tbl where id in(
 select min(id) 
 from (
  SELECT * 
  FROM tbl 
  where (filterName = 'inRockBand' and filterValue = 'true') 
     or filterName is null
 ) a 
 group by keyId
);

edited Feb 2, 2023 at 13:22

mustaccio

28.9k24 gold badges60 silver badges77 bronze badges

answered May 29, 2022 at 14:31

Urbanleg

3751 gold badge4 silver badges16 bronze badges

Add a comment |

Stack Exchange Network

MYSQL - Querying a table with optional filters and default value

2 Answers 2

Your Answer

Hot Network Questions

MYSQL - Querying a table with optional filters and default value

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions