CODEWITHSUNDEEP
laraveleloquent
David Heremans
David Heremans

Reputation: 671

How to link subquery in Laravel QueryBuilder to outer table

I'm looking on how I can have a subquery in Laravel QueryBuilder join with the outer table. For instance, we have a table with Exchange Rates, with a currency and a value_date.

For each currency, we want to be able to fetch the valid exchange rate on a certain valuedate. Since exchange rates do not change on non working days, that means that for saturday and sunday, the value of friday is still valid. A bit of an obvious and standard case.

In SQL, I would query this as follows:

SELECT currency, value_date, rate
FROM exchange_rates er
WHERE value_date = (
    SELECT max(value_date)
    FROM exchange_rates
    WHERE currency = er.currency
    AND value_date <= '2019-02-03'
)

This would return a list of all exchange rates (one record for each currency), with the rate that is valid on 2019-02-03, and the associate value date (which would probably be 2019-02-01, since 2019-02-03 is a sunday...)

I have no idea however how I can do this with the Eloquent QueryBuilder without falling back to doing raw sql queries...

$value_date = '2019-02-03';
App\ExchangeRate::where('value_date', function($query) use ($value_date) {
    ... ??? ...
});

Upvotes: 2

Views: 457

Answers (1)

Mozammil
Mozammil

Reputation: 8750

where() can also accept a closure that would allow you to do this: 

return ExchangeRate::select('currency', 'value_date', 'rate')
    ->where('value_date', function($query) {
        $query->from('exchange_rates AS er')
            ->selectRaw('max(value_date)')
            ->whereColumn('exchange_rates.currency', 'er.currency')
            ->where('value_date', '<=', '2019-02-03');
    })->get();

Here's the output of the query.

Edit: To assign an alias to the outer table, you could do that with the DB Query Builder.

return DB::table('exchange_rates AS alias')
    ->select('currency', 'value_date', 'rate')
    ->where('value_date', function($query) {
        $query->from('exchange_rates AS er')
            ->selectRaw('max(value_date)')
            ->whereColumn('alias.currency', 'er.currency')
            ->where('value_date', '<=', '2019-02-03');
    })->get();

Upvotes: 3

Related Questions