CODEWITHSUNDEEP
mysqllaravel
Hajae
Hajae

Reputation: 23

Converting mysql queries to laravel syntax

I don't know how to convert subquery to laravel query. How I can convert mysql query to laravel query? this is the query

select vd_id, subCount
from   (select vd_id, count(vd_id) subCount 
        from sell_data
        group by vd_id) sub
        where subCount = (select count(vd_id) maxCount
                          from sell_data
                          group by vd_id 
                          order by maxCount desc limit 1) 
           or subCount = (select count(vd_id) minCount
                          from sell_data
                          group by vd_id 
                          order by minCount asc limit 1) ;

Upvotes: 2

Views: 54

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

$from = DB::table('sell_data')->select('vd_id')->selectRaw('count(vd_id) subCount')
    ->groupBy('vd_id');
$results = DB::query()
    ->select('vd_id', 'subCount')
    ->fromSub($from, 'sub')
    ->where('subCount', function($query) {
        $query->selectRaw('count(vd_id) maxCount')
            ->from('sell_data')
            ->groupBy('vd_id')
            ->orderByDesc('maxCount')
            ->limit(1);
    })
    ->orWhere('subCount', function($query) {
        $query->selectRaw('count(vd_id) minCount')
            ->from('sell_data')
            ->groupBy('vd_id')
            ->orderBy('minCount')
            ->limit(1);
    })
    ->get();

Upvotes: 2

Related Questions