CODEWITHSUNDEEP
laraveleloquent
Zaiman Noris
Zaiman Noris

Reputation: 327

Nested Select in Eloquent

I want to recreate below SQL in Eloquent (Laravel 6 LTS)

I want to avoid DB::raw as I have logics behind the PartnerPrice::class (model)

SELECT *
    FROM (SELECT *,
                 ROW_NUMBER ()
                 OVER (PARTITION BY group, TYPE
                       ORDER BY effective_at DESC, created_at DESC)
                    r
            FROM partner_prices
           WHERE     group = 'premier'
                and partner_id = 8
                 AND TYPE = 'premium'
                 AND effective_at <= '2020-10-31') a
   WHERE r = 1
ORDER BY group;

Here's my working inner query. I just need help wrapping this with another select and add a where('r', 1)

$sub = PartnerPrice::select('*')
        ->selectRaw('ROW_NUMBER () OVER (PARTITION BY mccmnc, TYPE ORDER BY effective_at DESC, created_at DESC) r')
        ->where('type', $type)
        ->where('partner_id', $partnerId)
        ->where('group', $group)
        ->where('effective_at', '<=', now()->subMonth()->lastOfMonth())
        ->get();

Upvotes: 1

Views: 1289

Answers (1)

Mohamed Mufeed
Mohamed Mufeed

Reputation: 1560

You could use the toSql method with mergeBindings like this

$queryBuilder = PartnerPrice::select('*')
        ->selectRaw('ROW_NUMBER () OVER (PARTITION BY mccmnc, TYPE ORDER BY effective_at DESC, created_at DESC) r')
        ->where('type', $type)
        ->where('partner_id', $partnerId)
        ->where('group', $group)
        ->where('effective_at', '<=', now()->subMonth()->lastOfMonth());

$result = DB::table(DB::raw('(' . $queryBuilder->toSql() . ') as a'))
    ->mergeBindings($queryBuilder->getQuery())
    ->where('a.r', 1)
    ->get();

Note that I omitted the get() on the first builder

Also I think you can use

DB::select('*')
    ->fromSub($queryBuilder, 'a')
    ->where('a.r', 1)
    ->get();

But never used it. try this too.

Upvotes: 1

Related Questions