Level 75
$teamIds = Team::where('options->stage', 'Subscribed')->pluck('id');
$documents = Document::where(function ($query) {
$query->where('stage', '!=', 10)->orWhere('error', '!=', '');
})->whereIn('team_id', $teamIds)->get();
Oct 20, 2020
7
Level 4
Query with where + where or + where in not working
I am having some trouble getting this to respond correctly:
$teams = Team::where('options->stage', 'Subscribed')->get()->pluck('id')->toArray();
$str = implode(", ", $teams);
$documents = Document::where('stage', '!=', 10)->orWhere('error', '!=', '')->whereIn('team_id', [$str])->get();
The query output is this:
select
*
from
`documents`
where
(
`stage` != 10
or `error` != ''
and `team_id` in ('2, 7, 9, 24, 29, 32, 47')
)
and `documents`.`deleted_at` is null
But its returning documents where team_id is not in the array.
Level 104
The team_id condition should be outside the stage/error conditions?
Document::where(function (Builder $builder) {
$builder->where('stage', '!=', 10)->orWhere('error', '!=', '');
})->whereIn('team_id', [$str])->get();
resulting in:
select *
from `documents`
where (`stage` != 10 or `error` != '')
and `team_id` in ('2, 7, 9, 24, 29, 32, 47')
and `documents`.`deleted_at` is null
Level 4
Both of the above return returned 0 results - But the issue is with MySQL I think - as it the query only returns the expected results when you set one team ID, not multiple.
Does not work:
select
id, team_id
from
`documents`
where
(
`stage` != 10
or `error` != ''
)
and `documents`.`team_id` in ('2, 7, 9, 24, 29, 32, 45, 47')
and `documents`.`deleted_at` is null
Works:
select
id, team_id
from
`documents`
where
(
`stage` != 10
or `error` != ''
)
and `documents`.`team_id` in ('47')
and `documents`.`deleted_at` is null
Level 102
Yes. this is the problem as you are setting one big quote around the whole thing meaning the team_id is ALL ids at once
and `documents`.`team_id` in (2, 7, 9, 24, 29, 32, 45, 47)
The answer by Michal should work as that has correctly set ids as an array :)
Level 104
Correct @sinnbeck - missed that myself whenever I was wrapping the orWhere constraint.
Level 4
Missed that too!
Works as expected now.
Many thanks
Level 104
@utchin you could also consider a single query using a subquery if $teamIds is only used to get that list of ids.
Please or to participate in this conversation.