How to Write Select Query with Subquery Using Laravel Eloquent Querybuilder

How to select from subquery using Laravel Query Builder?

In addition to @delmadord's answer and your comments:

Currently there is no method to create subquery in FROM clause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings:

$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
    ->count();

Mind that you need to merge bindings in correct order. If you have other bound clauses, you must put them after mergeBindings:

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )

    // ->where(..) wrong

    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder

    // ->where(..) correct

    ->count();

Laravel Subquery with Query Builder

You should create a relationship between your User model and the model for the covis_transactions table. (I'm gonna call it CovisTransaction)

# User.php
public function covis_transactions()
{
    return $this->hasMany(CovisTransaction::class);
}

Then, you can use withCount to get the aggregate count.

User::query()
    ->select('id', 'nip', 'name')
    ->withCount('covis_transactions as total_survey')
    ->where('user_role_id', 7)
    ->groupBy('id')
    ->get();

---

• https://laravel.com/docs/9.x/eloquent-relationships#one-to-many
• https://laravel.com/docs/9.x/eloquent-relationships#aggregating-related-models

How to Select from SubQuery using distinct in Laravels

You can use subquery tables as closures or passing them as builder objects.

• DB::table(Closure, alias) or DB::query()->from(Closure, alias)
• DB::table(Builder, alias) or DB::query()->from(Closure, alias)

$subquery = DB::table('warga')
    ->select('no_card', 'description')
    ->distinct();

$results = DB::table($subquery, 't')
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->groupBy('description')
    ->get();

$results = DB::table(function ($query) {
        $query->from('warga')
            ->select('no_card', 'description')
            ->distinct();
    }, 't')
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->groupBy('description')
    ->get();

You can even make the query look very SQL-like if you want.

$query = DB::query()
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->from(function ($sub) {
        $sub->select('no_card', 'description')
            ->distinct();
    }, 't')
    ->groupBy('description')
    ->get();

How to do this in Laravel, subquery where in

Consider this code:

Products::whereIn('id', function($query){
    $query->select('paper_type_id')
    ->from(with(new ProductCategory)->getTable())
    ->whereIn('category_id', ['223', '15'])
    ->where('active', 1);
})->get();

How to write select query with subquery using laravel Eloquent Querybuilder?

Also show this answer and for your variant:

$subQuery = \DB::table('orders')->selectRaw('driver_id, created_at, COUNT(driver_id) AS total_delieveries')
    ->where('is_paid', 0)
    ->where('order_status', '5')
    ->whereBetween('created_at', [$first_Day, $last_Day])
    ->groupBy(\DB::raw('DATE_FORMAT(created_at ,"%Y-%m-%d"),driver_id'));

$q = \DB::table(\DB::raw('('.$subQuery->toSql().') as o1'))
    ->selectRaw('o2.driver_id,total_delieveries,DATE_FORMAT(o1.created_at ,"%Y-%m-%d") AS created_at')
    ->join('orders as o2', 'o1.driver_id', '=', 'o2.driver_id')
    ->groupBy('o1.created_at')
    ->mergeBindings($subQuery)
    ->get();

How to select data from a sub query using laravel query builder

Your code is already functional. The only alternative (to make the same query) is to inline the $subQuery part.

$subQuery = DB::table('table1')->groupBy('col');

$data = DB::table($subQuery, 'sub')->get();

Is the same as

$data = DB::table(function ($sub) {
        $sub->from('table1')
            ->groupBy('col');
    }, 'sub')
    ->get();

or

$data = DB::table(DB::table('table1')->groupBy('col'), 'sub')->get();

Laravel query Builder Select from subquery

I believe you can rewrite your query as correlated sub query to filter products with max value of year_production where names are duplicate

select p.product_id,
       p.name,
       coalesce(sum(o.amount),0) as amount
from products as p
left join orders o on p.product_id = o.product_id
where exists (
    select 1
    from products p1
    where p1.name = p.name
    having max(p1.year_production) = p.year_production
)
group by p.product_id, p.name
order by p.product_id

and in query builder you can transform it as

$products = DB::table('products as p')
               ->select(['p.product_id',
                         'p.name',
                          DB::raw('coalesce(sum(o.amount),0) as amount')
                        ])
               ->leftJoin('orders as o', 'p.product_id', '=', 'o.product_id' )
               ->whereExists(function ($query) {
                   $query->select(DB::raw(1))
                         ->from('products as p1')
                         ->whereRaw(DB::raw('p1.name = p.name'))
                         ->havingRaw('max(p1.year_production) = p.year_production')
                         ;
              })
              ->groupBy('p.product_id','p.name')
              ->orderBy('p.product_id')
              ->get();

---

Related Topics