How to join on a select result with eloquent in Laravel?

Question

I want to join on a result of other select like this :

SELECT *
  FROM TABLE1
  JOIN (
  SELECT cat_id FROM TABLE2 where brand_id = 2 GROUP BY TABLE2.cat_id) AS b ON TABLE1.id = b.cat_id

is there any way to do this with eloquent?

Answer 1

As it is mentioned here, using DB:raw() will solve your problem.

DB::table('table1')->join(DB::raw("(SELECT 

          cat_id

          FROM table2

          WHERE brand_id = 2   

          GROUP BY table2.cat_id

          ) as b"),function($join){

            $join->on("b.cat_id","=","table1.id");

      })->get();

Answer 2

\DB::table('table1')->join('table2' , function($join){
   $join->on('table1.id', '=', 'table2.cat_id');
})->select(['table2.cat_id' , 'table1.*'])
->where('table2.brand_id' , '=' , '2')
->groupBy('table2.cat_id');

Depends on whether brand_id is in table1 or table2

You can also use model approach for it.

TABLE1::join('table2' , function($join){
   $join->on('table1.id', '=', 'table2.cat_id');
})->select(['table2.cat_id' , 'table1.*'])
->where('table2.brand_id' , '=' , '2')
->groupBy('table2.cat_id');