Level 33
hello @ahmedelgohary
Please share your model and migration
Jun 23, 2020
6
Level 1
return more than attribute while grouping by from 3 tables
i would like to return all reservations that were done by all employees that existing in specific branch ((daily)) so there are 3 tables : ticket_reservations , employees , branches employees 1 to mini ticket_reservations branchs 1 to mini emloyees
public function RetreiveMoneyDaily(){
$TicketReservations = DB::table('ticket_reservations')
->select('ticket_reservations.created_at', DB::raw('SUM(price) as SumForToday'))
->join('employees', 'ticket_reservations.employee_id' , '=' , 'employees.id' )
->join('branches', 'employees.branch_id', '=', 'branches.id')
->groupBy('ticket_reservations.created_at')
->get();
return $TicketReservations;
}
i would like to get branches.id , ticket_reservations.price , employee.name
Thanks in Advance .
Level 6
If you like to get branches.id , ticket_reservations.price , employee.name you will need to group by by branches.id and employee.id to, assuming you wanna sum up ticket_reservations.price.
Also to group by daily you have to convert the timestamp created_at to date: DATE(ticket_reservations.created_at);
If I understood you correctly your query will look something like this:
DB::table('ticket_reservations')
->select(DB::raw('DATE(ticket_reservations.created_at) as day'), 'branches.id', 'employee.name', DB::raw('SUM(price) as SumForToday'))
->join('employees', 'ticket_reservations.employee_id' , '=' , 'employees.id' )
->join('branches', 'employees.branch_id', '=', 'branches.id')
->groupBy('day')
->groupBy('branches.id')
->groupBy('employee.id')
->get();
Level 1
Ticket_reservations
Level 1
@kalemdzievski Hello
This is mine but i returns all dates without grouping like return 2 of 01/06/2020. 2 of 21/06/2020.. etc
$firstDayofThisMonth = Carbon::now()->firstOfMonth()->toDateString();
$currentDayofThisMonth = Carbon::now()->toDateString();
$Money = DB::table('ticket_reservations')
->join('employees', 'ticket_reservations.employee_id' , '=' , 'employees.id' )
->join('branches', 'employees.branch_id', '=', 'branches.id')
->where('employees.branch_id',$id)
->whereBetween('ticket_reservations.created_at', [$firstDayofThisMonth, $currentDayofThisMonth])
->select('branches.name','branches.location', 'ticket_reservations.created_at','ticket_reservations.price')
->groupBy('branches.name','branches.location', 'ticket_reservations.created_at','ticket_reservations.price')
->orderBy('ticket_reservations.created_at')
->paginate(5);
return view('admin.branches.show',['Money'=>$Money, 'id' => $id]);
Level 1
i would like to select the price without groupBy(ticket_reservations.price) but i receive this error if i remove the selected items from groupBy items
SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.ticket_reservations.price' isn't in GROUP BY (SQL: select branches.name, branches.location, ticket_reservations.created_at, ticket_reservations.price from employees inner join ticket_reservations on employees.id = ticket_reservations.employee_id inner join branches on employees.branch_id = branches.id where employees.branch_id = 10 and ticket_reservations.created_at between 2020-06-01 and 2020-06-23 group by branches.name, branches.location, ticket_reservations.created_at)
Level 6
If you do not sum up the ticket_reservations.price then you have to include the ticket_reservations.price in the group by.
Why do you want to group if you are not using any aggregate functions?
1 like
Please or to participate in this conversation.