How to write the below query using subquery?-CodePudding

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;";
}

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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;
}