'How to write the below query using subquery?

I am trying to fetch currency from country table if row exists else return null as written in below code.

$country = Country::where("id", 619);
if($country->exists()){
    $currency = $country->first()->currency;
} else {
    $currency = "USD;";
}
laraveleloquentsubquery


Solution 1:[1]

An easy to write solution would be

$currency = Country::where("id", 619)->value('currency') ?? 'USD;';

This will not load an instance of Country (doesnt use first()) and will return the value of the "currency" attribute or null if no result is available.

Solution 2:[2]

You can use this shortcut

$currency = Country::where("id", 619)->first()->currency ?? 'USD';

This will return the currency if country exists and if it doesn't exist it will return USD

Solution 3:[3]

You have to first add a relation in your model. You can find this in the documentation of Laravel. After this you have multiple ways to do this, for only a check the best way is the ::has($relation) function that you can find here.

Another option you have is to join the table with the function ::with($relation). After doing this you can check the columns of the joined table with the ::where($column, $value) function like you are used to. I think this answers your question how to make a subquery.

Example of the relation function in the model class.

function currency() {
  return $this->hasOne(Country:class, 'code', 'country');
}

Example of a subquery

$hasCurrency = Country::has('currency');
$currency = null;

if ($hasCurrency) {
  $result = Country::with('currency')
    ->where('id', 619)
    ->where('currency.active', 1)
    ->first();

  $currency = $result->currency->code;
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 N69S
Solution 2 Mohammed_40
Solution 3 N. Hamelink

Related Questions