How to do two clauses WHERE inside a nested WITH

user_8710193 0
Viewed 49

I have the following code:

$sectors = Sector::where('company_id', $id)
                 ->where('status', '0')
                 ->with('charges.levels')
                 ->get();

and I need 3 conditions

  • Status of sector be 0
  • Status of charges be 0
  • Status of levels be 0

So I wanna know:

  • How to use a WHERE that search the status of charges 0 and the status of levels 0 when they are in a nested with: ->with('charges.levels')

The code have the following relationships:

  • A sector has many charges and a charge belongs to a sector
  • A charge has many levels and a level belongs to a charge

So, I wanna bring all levels where status of level is 0, status of charge is 0 and the status of sector is 0

eloquent laravel php
2 Answers

You can query on the relations like this when you are eager loading:

$sectors = Sector::where('company_id', $id)
    ->with([
        'charges' => function ($query) {
            $query->where('status', 0);
        },
        'charges.levels' => function ($query) {
            $query->where('status', 0);
        }
    ])
    ->get();

For more information about constraining eager loading: https://laravel.com/docs/5.7/eloquent-relationships#constraining-eager-loads

user_55564400 
$sectors = Sector::where('company_id', $id)->where('status', 0)
    ->whereHas('charges', function (\Illuminate\Database\Eloquent\Builder $query) {
        $query->where('status', 0)
            ->whereHas('levels', function (\Illuminate\Database\Eloquent\Builder $query) {
                $query->where('status', 0);
            });
    })->get();

https://laravel.com/docs/5.7/eloquent-relationships#querying-relationship-existence

Be careful with this though. Eloquent doesn't do JOIN statements (except in the case of belongsToMany relations). with() merely executes a separate queries that uses IN() statements populated by previous ones, and whereHas() will produce a subquery. So the above will produce a query that'll look something along the lines of:

SELECT *
FROM sector
WHERE
    status = 0
    AND EXISTS (
        SELECT *
        FROM charge
        WHERE
            charge.sector_id = sector.id
            AND status = 0
            AND EXISTS (
                SELECT *
                FROM levels
                WHERE
                    levels.charge_id = charge.id
                    AND status = 0
            )
    );

I don't know about other DB engines, but MySQL isn't terribly good at handling subqueries. If you were to do an EXPLAIN on that, the number of initial entries that get pulled for the query would be limited only by sector.status, which can be a problem if that table is large and/or sector.status is not indexed. This is as opposed to say, a JOIN query that could get the full benefit of combining search criteria. With JOINs, additional criteria typically improve performance, but the reverse is true with EXITSs. So you might have better luck using this: https://laravel.com/docs/5.7/queries#joins.

user_21373160
Related