How to list items while related items has or has no data? For example: I have 3 rooms. 1 of 3 rooms has no log. I want to list all rooms. The result: - room 1 (no log) - room 2 (last log is 2015-01-08 20:30:0) - room 3 (last log is 2015-01-09 07:20:15)
My current code: $query = \Rooms::query() ->related('logs'); // filter to display only log that last add fewer or equal than selected date/time. // any rooms that has not found any log should display only room name. $query->where_open(); $query->where('log_add', '<=', $selected_date) ->or_where('log_add', null); $query->where_close(); $result = $query->get();
my data in log table: log_id 1, log_add 999999999
What i got is - room 2 (last log is 2015-01-08 20:30:0) - room 3 (last log is 2015-01-09 07:20:15) No room 1. I want room 1 to display but no log data.
If I check the code, both has_many and many_many should construct a LEFT JOIN, so it should include the parent even if it doesn't have any children.
I have a feeling it's your filter the causes the problem.
What I usually do is enable the profiler, and run the generated query in something like phpmysqladmin. Then I tune it to get the answer I want, and then convert the SQL back into an ORM statement. If possible.
relation is one to many 1 room has many room_id in logs table.
In case that there is data in logs table and conditions met. The result is correctly. If there is nothing in logs table (empty), The result is displaying correctly. If there is a data in logs table but conditions not met (`log_add` is newer than selected date). The result will skip that room as example in "What i got is".
Donate
