[FIXED] How to get fallback row in laravel query builder?

Issue

i have a database table in following format.

| from_zone | from_type | to_zone | to_type  | client_id | rate |
|-----------|-----------|---------|----------|-----------|------|
| north     | national  | north   | national | 1         | 12   |
| north     | national  | north   | metro    | 1         | 15   |
| north     | national  | north   | metro    | null      | 21   |

Model - Drate, table - drates

to get rate for client id 1 we have the following query

$drate=Drate::where('from_zone','north')->where('from_type','national')
->where('to_zone','north')->where('to_type','metro')
->where('client_id',1)->get();
dd($drate->rate);
// output is 15

how to get fallback rate if no client id is provided (i.e. 21) ? if client id provided rate is 15.

Solution

Welcome to SO.

One possible solution would be to conditionally add the where('client_id',1) clause.

$drate = Drate::where('from_zone','north')
    ->where('from_type','national')
    ->where('to_zone','north')
    ->where('to_type','metro');

if( client id provided condition is true ) // pseudo-code
    $drate->where('client_id',1);

$drate = $drate->get();

Edit: Keep in mind that this will result in multiple matches. Both rows in your example will be matched.

You could if/else the query to only return when client_id = null when it is not provided whereNull('client_id').

if( client id provided condition is true ) // pseudo-code
    $drate->where('client_id',1);
else
    $drate->whereNull('client_id');

then again, it could also match other entries with the same condition (eg. multiple rows that have client null).

Decide what the result should be when client_id = null, and what sorting if you require multiple results.

For example, if you require that client_id = null should only return 1 result and that result should be the highest rate, you can combine orderBy('rate', 'desc') with first() instead of get();

Answered By - droplet

Answer Checked By - Mildred Charles (PHPFixing Admin)