Where condition is negating left join

Question

I currently have this code using Eloquent:

CustomerSource::leftJoin("booking", "booking.customer_source_id", "=", "customer_source.id")
->selectRaw("customer_source.text, count(booking.id) as count")
->groupBy("text")
->where([
    ["booking_date", ">=", $from],
    ["booking_date", "<=", $to],
])->get()

with $to and $from being dates.

I need the query so that it counts the number of bookings made in a date range for each customer source.

When I remove the where condition, it lists all Customer sources, and the counts of bookings as it should (some can be 0), but when I include the where condition, it treats the query as if the join is an inner join and any customer sources that do not have any related bookings for the date period are not in the result set.

Please could you help me in figuring out the issue?

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jun 25 '20 at 09:02

score 0 · Answer 1 · answered Jun 24 '20 at 14:55

0

You might be able to get what you're looking for by using whereBetween() and feeding in dates:

CustomerSource::leftJoin("booking", "booking.customer_source_id", "=", "customer_source.id")
  ->selectRaw("customer_source.text, count(booking.id) as count")
  ->groupBy("text")
  ->whereBetween('booking_date', [$from, $to])
  ->get();

answered Jun 24 '20 at 14:55

ediblemanager

36
1
4

Hi, thank you for your answer! Unfortunately, it still doesn't seem to work – mysterymyster Jun 25 '20 at 07:17

score 0 · Answer 2 · answered Jun 25 '20 at 07:44

Fixed by using left join closure.

CustomerSource
::leftJoin("booking", function ($join) use ($from, $to){
    $join->on("booking.customer_source_id", "=", "customer_source.id")
        ->whereBetween("booking_date", [$from, $to]);
})
->selectRaw("customer_source.text, count(booking.id) as count")
->groupBy("text")
->get()

Where condition is negating left join

2 Answers2