How to do advanced data filtering across multiple fields?

Question

I have a table from which I need to get results varying by certain user data:

I need to return the rows where each column or hit with the respective user parameter or is empty (null). _{Consider as user parameters the bottom of the image - USER_ID=29, USER_GROUP=ADMIN, etc..}

In the above case, return only Ids 30, 31, 33 and 36.

Answer 1

The query below does exactly what is proposed in the statement:

SELECT *
FROM USER
WHERE (USER_ID = 29 OR USER_ID IS NULL)
AND (USER_GROUP = 'ADMIN' OR IFNULL(USER_GROUP, '') = '')
AND (find_in_set('1', USER_LEVEL) OR IFNULL(USER_LEVEL, '') = '')
AND (find_in_set('3', USER_TYPE) OR IFNULL(USER_TYPE, '') = '')
AND (find_in_set('10', USER_ROLE) OR IFNULL(USER_ROLE, '') = '')

You can check the correct return on Sqlfiddle.

Answer 2

This is a little weird and doesn’t have enough information but come on.

select * from tabela where user_id = $user_id || user_group = $user_group || find_in_set($user_level, user_level) || find_in_set($user_type, user_type) || find_in_set($user_role, user_role) || (user_id = null && user_group = null && user_level = null && user_type = null && user_role)

I put in the Github for future reference.

This works if these fields that may have more than one value have only one digit.

I interpreted the rules as I could visualize in the question. If this is not it you should make the rules clearer. You even said where this information comes from, I assumed again. I also assumed that the void would be null.

Answer 3

The query below returns the records where each field is either equal to the parameter or is null. The function find_in_set Mysql does the trick of searching for a value in a comma-separated text list.

select
    ID, USER_ID, USER_GROUP, USER_ROLE, USER_LEVEL, USER_TYPE
from
    tabela
where
    (USER_ID = 29 OR USER_ID is null)
    and (USER_GROUP = 'ADMIN' OR USER_GROUP is null)
    and (find_in_set(1, USER_LEVEL) OR USER_LEVEL is null)
    and (find_in_set(3, USER_TYPE) OR USER_TYPE is null)
    and (USER_ROLE = 10 OR USER_ROLE is null)

See working on sqlfiddle.

Answer 4

I believe this is it, I will answer in PURE SQL

   SELECT * FROM nomeDaTable WHERE USER_ID = 29 OR USER_GROUP = "ADMIN" OR USER_LEVEL = 1 OR USER_TYPE = 3 OR USER_ROLE = 10 OR (USER_ID = "" && USER_GROUP = "" && USER_LEVEL = "" && USER_TYPE = "" && USER_ROLE = "")