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?

What's your data model like exactly? – Ali Gajani Apr 17 '18 at 07:11 — Ali Gajani, Apr 17 '18 at 07:11

Syed Abdur Rehman Kazmi · Answer 1 · 2018-04-17T06:50:35.470

1

\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');

edited Apr 17 '18 at 06:50

answered Apr 17 '18 at 06:32

Syed Abdur Rehman Kazmi

1,640
3
13
30

Actually that's not eloquent (what you call "Model approach"), but query builder. – Amarnasan Apr 17 '18 at 06:32
you want to do it with relations ? – Syed Abdur Rehman Kazmi Apr 17 '18 at 06:35
this is not with eloquent and not give me answer like query. – Hamid Naghipour Apr 17 '18 at 06:38
my answer is record of table1 but this give me only cat_id from table2 like this : array:2 [▼ 0 => {#615 ▼ +"cat_id": 184 } 1 => {#614 ▼ +"cat_id": 185 } ] – Hamid Naghipour Apr 17 '18 at 06:40
do u want to do it with relations ? @HamidNaghipour – Syed Abdur Rehman Kazmi Apr 17 '18 at 06:43
that's not important only give answer like down answer. – Hamid Naghipour Apr 17 '18 at 06:45
i have given the answer. just add the values you want from the other table in the select like select(['table2.cat_id' , 'table1.*']); – Syed Abdur Rehman Kazmi Apr 17 '18 at 06:49

Sakibur Rahman · Accepted Answer · 2018-04-17T07:47:28.667

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();

edited Apr 17 '18 at 07:47

answered Apr 17 '18 at 06:38

Sakibur Rahman

834
3
10
26

how can I add where brand_id = 2 inside of this query? – Hamid Naghipour Apr 17 '18 at 07:41
brand_id is a column of which table? table1 or 2? – Sakibur Rahman Apr 17 '18 at 07:44
do you know how can I get result in colloction or array? for now I have answer like this : 0 => {#614 ▼ +"id": 184 – Hamid Naghipour Apr 17 '18 at 07:50
Well, you can add a toArray() like this to convert the result into an array. `DB::table('table1')->.................->get()->toArray();` – Sakibur Rahman Apr 17 '18 at 07:53
no, I want to convert stdclass to collection, to Array only give me first level of result in array format, I want to get all result in Collection or array – Hamid Naghipour Apr 17 '18 at 07:55

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

2 Answers2