Eloquent orWhereNull when where has no results

Hi guys,

Is it possible to create a where clause which first attempts to find a row matching a client_id, and when non is found performs a orWhereNull?

Below is the query i am currently working with, on line two i initiate a where statement which should do the above, however currently it retreives both the row with and without a client_id.

$q = Cns::with(['rates' => function ($a) use ($client) {
            $a->where(function($b) use ($client) {
                $b->where('client_id', $client->id)
                    ->orWhereNull('client_id');
            });
            $a->whereNull('subordinates');
            $a->where(function($b) {
                $b->where('valid_from', '<=', Carbon::now()->format('Y-m-d'))
                    ->orWhereNull('valid_from');
            });
            $a->orderBy('cns_id', 'asc');
            $a->orderBy('valid_from', 'desc');
        }])->with(['futureRates' => function ($a) use ($client) {
            $a->where('client_id', $client->id);
            $a->whereNull('subordinates');
            $a->where('valid_from', '>', Carbon::now()->format('Y-m-d'));
            $a->orderBy('cns_id', 'asc');
            $a->orderBy('valid_from', 'desc');
        }])
            ->where('descript', 'like', '%'. $country->name .'%');

The table structure for the 'rates' table looks like this:

id, cns_id, client_id, subordinates, etc., etc.
-------------------------------------------------
1, 6, 3, null, etc., etc.
2, 6, 5, null, etc., etc.
3, 6, 7, null, etc., etc.
4, 6, null, null, etc., etc.

This would be an example of what the query should do: cns_id=6 AND client_id=3 , if there is no client_id with 3 found, than take the row with cns_id=3 AND client_id is null

JarekTkaczyk

10 years ago

Level 53

@Qlic The easy way is to use relation has query:

...
            $a->where(function($b) use ($client) {
                $b->whereHas('client', function ($q) use ($client) {
                       $q->where('id', $client->id);
                })->orWhereNull('client_id');
            });
...

I assume you have a Rate belongsTo Client relation defined as client method.

Check the performance tho, as this might be slow on big table.

1 like

Qlic

10 years ago

Level 18

@JarekTkaczyk I tried your suggestion, i already had a client relation defined in the model however that is a hasOne (for a diffrent function of the site), so i added a clients method with a hasMany, and adjusted that in the query.

public function client()
    {
        return $this->hasOne(Client::class, 'id', 'client_id');
    }

    public function clients()
    {
        return $this->belongsTo(Client::class, 'client_id', 'id');
    }

Query now looks like this:

        $q = Cns::with(['rates' => function ($a) use ($client) {
            $a->where(function($b) use ($client) {
                $b->whereHas('clients', function ($c) use ($client) {
                    $c->where('id', $client->id);
                })->orWhereNull('client_id');
            });
            $a->whereNull('subordinates');
            $a->where(function($b) {
                $b->where('valid_from', '<=', Carbon::now()->format('Y-m-d'))
                    ->orWhereNull('valid_from');
            });
            $a->orderBy('cns_id', 'asc');
            $a->orderBy('valid_from', 'desc');
        }])->with(['futureRates' => function ($a) use ($client) {
            $a->where(function($b) use ($client) {
                $b->whereHas('clients', function ($c) use ($client) {
                    $c->where('id', $client->id);
                })->orWhereNull('client_id');
            });
            $a->whereNull('subordinates');
            $a->where('valid_from', '>', Carbon::now()->format('Y-m-d'));
            $a->orderBy('cns_id', 'asc');
            $a->orderBy('valid_from', 'desc');
        }])
            ->where('descript', 'like', '%'. $country->name .'%');

However, now it returns two rows for each cns. One with the client_id, and the default one without a client_id.

JarekTkaczyk

10 years ago

Level 53

@Qlic that relation should be belongsTo as well, not hasOne.

Anyway, show the tables with example data and your expected result, otherwise it will be quite hard.

Qlic

10 years ago

Level 18

@JarekTkaczyk

Screenshot of the cns table: http://i64.tinypic.com/20qjq0m.jpg

Screenshot of the rates table: http://i65.tinypic.com/2lmnkhe.jpg

Screenshot of the output: http://i68.tinypic.com/bhiuqr.jpg

The cns table is linked to cns_rates (id -> cns_id)

Each cns has a default entry in the cns_rates table, these default entries have a client_id & subordinates value of NULL.
Each reseller can have a custom rate for a cns, in that case the client_id will be an integer (the client_id), and subordinates will be NULL.
Each reseller can have a custom rate for it's clients per cns, in that case the client_id will be an integer (the client_id), and subordinates will be 1.

In the output screenshot, the custom rates for the selected reseller are orange, though the query also returns all the default rates. What should happen is the following: If the selected reseller has a custom rate, select it, else select the default rate for this cns.

Please or to participate in this conversation.