Eloquent query not returning the expected result

I'm trying to use this eloquent query to query a table in my database with an array of values,

FlightRequest::where('public', 1)
->where(function ($q) use ($query) {
    $q->whereIn('departure', $query);
    $q->orWhereIn('arrival', $query);
})
->whereNull('acceptee_id')
->orderBy('id', 'asc')
->get();

My query variable is an array of 2 values ['EGLL', 'EGKK'] in my FlightRequest table I have 1 row that has a public value of 1, a departure value of EGLL and the acceptee_id is null yet that does not get returned.

I have tried a dd on the query and it returns this SQL statement

select * from `flight_requests` where `public` = 1 and (`departure` in (EGLL, EGKK) or `arrival` in (EGLL, EGKK)) and `acceptee_id` is null order by `id` asc

I think the issue is down to the departure and arrival columns being JSON columns casted to arrays. I effectivley need to query an each element in my $query array with each element in the departure and arrival JSON arrays.

I have tried doing

$q->orWhereJsonContains('departure', $query);
$q->orWhereJsonContains('arrival', $query);

but that only returns a row where the arrival column is ['EGLL', 'EGKK'] i.e. where both arrays are identical

automica

5 years ago

Best Answer

Level 54

@hcphoon01 you'll need to loop through your array of data rather than passing it is as an array

$values = ['EGLL', 'EGKK'];

FlightRequest::where('public', 1)
    ->where(function ($query) use ($values) {
        foreach ($values as $value) {
            $query->orWhereJsonContains('departure', $value);
            $query->orWhereJsonContains('arrival', $value);
        }
    })
    ->whereNull('acceptee_id')
    ->orderBy('id', 'asc')
    ->get();

I've renamed your $query to $values as then it makes $query more consistent,

1 like

Please or to participate in this conversation.