Level 73
Which is the starting point when a person wants to reserve a desk?
I would start with the office, then the desk and lastly the reservation count
Jun 10, 2022
7
Level 13
How to structure this query?
I have three models Office, Desk, Reservation. An Office can have many Desks and a Desk belongs to an Office. A Desk can have many Reservations and a Reservation belongs to a Desk.
What I want to achieve now, is a query with such kind of result:
Office 1: 2022-06-10 -> 5 reservations, 2022-06-05 -> 2 reservations Office 2: 2022-06-10 -> 1 reservations, 2022-06-05 -> 3 reservations
Do I have to start the query at the office or at the reservations model? Can I achieve this within one query?
Level 13
@Tray2 Maybe my question is a little bit missleading. My goal is to create some kind of month based overview which office has how much reservation and colorize these days accordingly. So that a user can decide if he wants to book a desk in an office which is already crowded.
To achieve this, do I have to make three queries? One for the offices, one for the desks, and one for the reservations of a month and merge them together? Or can I achieve this within one query?
To reserve a desk on a specific day is already implemented.
Level 73
@wikorl A ok, no you can run that with a single query. My guess is tha you have the dates in your reservations table, that makes it a bit trickier, you need to fetch them first then which desk and which office.
Level 13
@Tray2 I managed to query the number of reservations based on the dates.
$result = Reservation::whereMonth('date', $this->currentMonth)
->groupBy('date')
->orderBy('date', 'ASC')
->get(array(
DB::raw('Date(date) as date'),
DB::raw('COUNT(*) as "reservations"')
));
But how do I group them now by office?
Level 39
Can I achieve this within one query?
Yes if you have the right relations.
For Eloquent, you should be able to do it with Has Many Trough ,
https://laracasts.com/discuss/channels/eloquent/how-to-structure-this-query
and add some joins, ...
Personally, I never use Eloquent for such query, I have better raw sql since is easier (for me) to write/read/understand.
Do you want some tips, for raw sql, or do you prefer to test by yourself first?
2 likes
Level 13
@sr57 Yes please provide me some tips.
Level 39
You should be able to adapt this query
$sql="SELECT t1.id,l.created_at,count(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.t1_id LEFT JOIN t3 ON t2.id=t3.t2_id GROUP BY t1.id.t3.created_at ORDER BY t1.id,t3.created_at";
with your tables
t1 = offices
t2= desks
t3= reservations
your fields
t1.id -> t1.office
t3.created_at -> t3.reservation_date
and your relations
t1_id=office_id
t2_id=desk_id
Than run this query with DB::select($sql);
If any difficulty share your code and your question / the error you get.
1 like
Please or to participate in this conversation.