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

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();

CodePudding user response：

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();

CodePudding user response：

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();

CodePudding user response：

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