Laravel left join Raw usning Query Builder

Question

Is there a way to left join a raw statement in Laravel using Query Builder?

I have table A:

   a_id  |  name  |
-----------------
   1     |  Foo   |
   2     |  Bar   |

and table B:

   a_id  |  status  |
---------------------
   1     |   true   |
   2     |   false  |
   1     |   false  |
   2     |   true   |
   2     |   false  |

and table C:

   a_id  |  status  |
---------------------
   1     |   true   |
   2     |   true   |
   2     |   false  |

Then A left join B left join C should results:

   a_id  |  b_count  |   c_count
---------------------------------
   1     |     2     |     1
   2     |     3     |     2

I know doing such thing is so simple in pure SQL statement, but I have to do it with Query Builder.

Omid · Accepted Answer · 2015-11-03 08:07:11Z

1

I found it myself:

Model::selectRaw('
    A.a_id, 
    count(DISTINCT B.a_id) AS b_count, 
    count(DISTINCT C.a_id) AS c_count
')
    ->leftjoin('B', 'B.a_id', '=', 'A.a_id')
    ->leftjoin('C', 'C.a_id', '=', 'A.a_id')
    ->groupby('A.a_id');

Using DISTINCT was the key point in this case of joining more than two tables.

answered Nov 3, 2015 at 8:07

Omid

4,7259 gold badges48 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel left join Raw usning Query Builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related