drewdan
8 months ago

SQL Query with ANDs ORs

Posted 8 months ago by drewdan

Hi All,

I am using the Eloquent Filter to filter my database query, however, I have been asked to add a search field which will search values between multiple columns and tables.

The problem I am getting is that I have had to use or operators to query between the different tables as we dont neccesarily know which field needs to be searched, so we have to search them all with the same data and return whichever matches.

But, we also filter by the top table by date, and some other status flags.

This is the query that gets generated by Eloquent Filter

select * from `interactions` where (`company_id` = 2 or exists (select * from `messages` where `interactions`.`id` = `messages`.`interaction_id` and `message` LIKE '%dolor%') or exists (select * from `interaction_data` where `interactions`.`id` = `interaction_data`.`interaction_id` and `data` LIKE '%dolor%') or exists (select * from `users` where `interactions`.`user_id` = `users`.`id` and (`first_name` in ('dolor') or `last_name` in ('dolor')) and `users`.`deleted_at` is null) and `created_at` between '2019-04-01 00:04:00' and '2019-04-11 09:04:00') order by `created_at` desc limit 20 offset 0

However, this returns the data that matches the or statements, but ignored the created_at search.

So, I played around with the SQL in the workbench, and the only way I can see to get this working properly is like so:

select * from `interactions`
where exists (
    select * from `messages` 
    where `interactions`.`id` = `messages`.`interaction_id`
    and `message` LIKE '%dolor%'
    AND (`interactions`.`company_id` = 2 and `interactions`.`created_at` between '2019-04-01 00:04:00' and '2019-04-11 09:04:00' )
) 
OR exists (
    select * from `interaction_data` 
    where `interactions`.`id` = `interaction_data`.`interaction_id` 
    and `data` LIKE '%dolor%'
    AND (`interactions`.`company_id` = 2 and `interactions`.`created_at` between '2019-04-01 00:04:00' and '2019-04-11 09:04:00' )
) 
OR exists (
    select * from `users` 
    where `interactions`.`user_id` = `users`.`id` 
    and (`first_name` in ('dolor') or `last_name` in ('dolor')) 
    and `users`.`deleted_at` is null
    AND (`interactions`.`company_id` = 2 and `interactions`.`created_at` between '2019-04-01 00:04:00' and '2019-04-11 09:04:00' )
)
AND (`company_id` = 2 and `created_at` between '2019-04-01 00:04:00' and '2019-04-11 09:04:00' )

order by `created_at` desc limit 20 offset 0

which is essentially injecting into every or query.

Is there a better way to do this query?

Please sign in or create an account to participate in this conversation.