I have 3 table. Users, Accounts and Booking. I have try three table join and year and month wise query. 3 Table diagram
But query booking calculation is show wrong. Its show double quantity.
$january = DB::table('users')
->join('bookings', 'users.id', '=', 'bookings.user_id')
->join('accounts', 'users.id', '=', 'accounts.user_id')
->orderBy('users.room_id','asc')
->groupBy('users.id')
->whereYear('bookings.bookingdate','=', '2016')
->whereMonth('bookings.bookingdate','=','01')
->whereYear('accounts.accountdate','=', '2016')
->whereMonth('accounts.accountdate','=','01')
->select('users.*',
DB::raw("COUNT(case when bookings.breakfast='on' then 1 else null end) AS t_breakfast"),
DB::raw("COUNT(case when bookings.lunch='on' then 1 else null end) AS t_lunch"),
DB::raw("COUNT(case when bookings.dinner='on' then 1 else null end) AS t_dinner"),
DB::raw("SUM(accounts.amount) AS t_amount")
)
->get();
dd($january);
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire