CODEWITHSUNDEEP
phplaraveleloquent
Leonardo Theodoro
Leonardo Theodoro

Reputation: 73

How to do two clauses WHERE inside a nested WITH

I have the following code:

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

and I need 3 conditions

So I wanna know:

The code have the following relationships:

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

Upvotes: 2

Views: 57

Answers (2)

kmuenkel
kmuenkel

Reputation: 2789

$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.

Upvotes: 0

Chin Leung
Chin Leung

Reputation: 14941

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

Upvotes: 1

Related Questions