How do I wrap Laravel eloquent conditions to create the following query

I have a simple results table that looks like the following –

id, home_team_id, away_team_id, home_score, away_score, media
1       511          322            4           0       'a4vw'
2       232          511            2           2       'bf34',
3       111          511            2           3        NULL

I am able to get all the rows where ‘team_id’ value is either the home_team_id OR away_team_id = 511, but I also need to check to ensure that the media column is NOT NULL
The raw SQL query would like like this

SELECT * FROM results where (home_team_id = 310718 OR away_team_id = 310718) and media is not null;

However I am trying to use the Laravel Eloquent approach as much as possible as I’m learning this so could use one approach like the following (which works fine) but i’m trying to not use any DB::raw methods in my approach

DB::table("results")
->where(DB::raw("(home_team_id = 511 or away_team_id = 511)"))
->whereNotNull("media")
->get();

How would I amend the following in my Results model to perform the same query as the queries above as this returns all the results I only expect 2 results as media column has a single NULL entry

return Result::where('home_team_id', '=', $clubId)
             ->orWhere('away_team_id', '=', $clubId)
             ->whereNotNull('media')
             ->get();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Could you try this please?

return Result::where(function($query) use ($clubId) {
            $query->where('home_team_id', $clubId)
                ->orWhere('away_team_id', $clubId);
        })->whereNotNull('media')->get();

Method 2

The problem in your query is the use of the orWhere condition in the wrong position. As there are two main conditions in your query one is

‘team_id’ value is either the home_team_id OR away_team_id

while the second condition is

media column is NOT NULL

So you will have to break down your two conditions into two separate conditional statements.

Try the following query

return Result::where(function($query) use ($clubId) {
   $query->where('home_team_id', '=', $clubId)
         ->orWhere('away_team_id', '=', $clubId);
})
->whereNotNull('media')
->get();

Method 3

It is a common mistake among beginners.

You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

https://laravel.com/docs/8.x/queries#logical-grouping

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0