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.

Answer 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.