Murlidhar Fichadia Murlidhar Fichadia - 7 months ago 7
SQL Question

two one to many relations - each Event has many spots and each spot has many bookings

I have three tables : Events, Spots and Bookings.
Events has many spots(datetime)and
Spots has many bookings.

I have 'spaces' column in spots table and 'spot_id' in bookings table. And In spots table I need to Sum all the spaces GROUP BY event_id and in bookings table I need to count all the bookings GROUP BY event_id. both the tables have event_id.

I need to query something like:

select S.event_id, sum(S.spaces), count(B.spot_id)
from spots S, bookings B
where S.event_id = B.event_id;


I need output like:

Event_id sum(Spaces) count(Bookings)
1 300 60
2 450 120


But because its one to many relation, I get sum of spaces wrong. if I hve 60 bookings = 60 rows therefore 60 * actual-sum_of_spaces. That is because I have multiple spots for single Event.

How do I get sum of spaces from table spots where event_id and sametime Get count of all booking given event_id. No. of rows in spots table dont match no.of rows in bookings.

bookings table

spotstable

Answer

Try this:

$events = DB::table('bookings')
             ->join(DB::raw('(select S.event_id, sum(S.spaces) as sum_spaces from spots S group by S.event_id) d'), function($join)
             {
                $join->on('bookings.event_id', '=', 'd.event_id');
             })
             ->select('bookings.event_id', 'd.sum_spaces',
                      DB::raw('COUNT(bookings.event_id)'))
             ->groupBy('bookings.event_id')
             ->get();
Comments