Level 23
wheres your code?
Apr 11, 2019
2
Level 15
SQL Query with ANDs ORs
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?
Level 15
It transpires that this cannot easily be done without somehow putting a load of joins in the search, but that gets pretty messy because they are lots of one to many joins.
For reference though, the query was being built up like this:
/**
* Filter interactions by message content
*
* @param string $daterange
* @return Illuminate\Database\Eloquent\Builder
*/
public function message($val)
{
$this->orWhereHas('messages', function ($query) use ($val) {
return $query->where('message', 'LIKE', '%' . $val . '%');
});
}
/**
* Filter interactions by data content
*
* @param string $daterange
* @return Illuminate\Database\Eloquent\Builder
*/
public function interactionData($val)
{
$this->orWhereHas('interactionData', function ($query) use ($val) {
return $query->where('data', 'LIKE', '%' . $val . '%');
});
}
In the end we changed the system to have a general search and then a filter area instead.
Please or to participate in this conversation.