CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Matthew
Matthew

Reputation: 1655

Join within join and count of records using SQL in Laravel

I've gotten stumped by this SQL query for my Laravel project, so first I'll describe the relationships.

cartages has the following fields: id, date_ending

cartage_batches has the following: id, cartage_id

cartage_items has the following: id,batch_id,amount

What I would like to be able to do is query the cartage table to be grouped into year and month pulled from the date_weekending field. But also grab the count of cartage_items in the cartage_batches in each months cartages.

I know it would involve a join within a join but I'm not entirely certain how to accomplish that best from within Laravel.

Example Data:

Cartage

id   date_ending
1    2019-01-01
2    2019-01-08
3    2019-03-04

Cartage_Batches

id   cartage_id
1    1
2    1
3    1
4    2
5    2

Cartage_Items

id   batch_id   amount
1    1          20.00
2    2          5.00
3    2          15.00
4    4          13.00

Expected output

2019-01 => ['count' => 4],
2019-03 => ['count' => 0]

Upvotes: 0

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Here is a raw MySQL query which should generate the result you want:

SELECT
    DATE_FORMAT(c.date_ending, '%Y-%m') AS ym,
    COUNT(ci.batch_id) AS count
FROM Cartage c
LEFT JOIN Cartage_Batches cb
    ON cb.cartage_id = c.id
LEFT JOIN Cartage_Items ci
    ON ci.batch_id = cb.id
GROUP BY
    DATE_FORMAT(c.date_ending, '%Y-%m');

screen capture of demo

Demo

Here is what your Laravel code might look like:

$result = DB::table('Cartage c')
    ->leftJoin('Cartage_Batches cb', 'cb.cartage_id', '=', 'c.id')
    ->leftJoin('Cartage_Itmes ci', 'ci.batch_id', '=', 'cb.id')
    ->groupBy(DB::raw("DATE_FORMAT(c.date_ending, '%Y-%m')"))
    ->select(DB::raw("DATE_FORMAT(c.date_ending, '%Y-%m'), COUNT(ci.batch_id) AS count"))
    ->get();

Upvotes: 2

Related Questions