I'm now curious (but too lazy to setup and test it) how the following query would fare:
SELECT meal_items.*, employee_markouts.employee_id, customer_orders.customer_id
FROM meal_items
LEFT JOIN employee_markouts ON employee_markouts.meal_item_id = meal_items.id
LEFT JOIN employees ON employees.id = employee_markouts.employee_id
LEFT JOIN customer_order_items ON customer_order_items.meal_item_id = meal_items.id
LEFT JOIN customer_orders ON customer_orders.id = customer_order_items.customer_order_id
LEFT JOIN customers ON customers.id = customer_orders.customer_id
WHERE (employees.store_id = 250 AND
employee_markouts.created >= '2021-02-03' AND
employee_markouts.created < '2021-02-04') OR
(customers.store_id = 250 AND
customer_orders.created >= '2021-02-03' AND
customer_orders.created < '2021-02-04')
Author here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45):
To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.
this is probably how I'd write it (assuming pg properly pushes the predicates down to the subquery in the lateral join)
select mi.*, x.employee_id, x.customer_id
from meal_items mi
join lateral (
select e.store_id, em.created, em.employee_id, customer_id = null
from employee_markouts em
join employees e on e.id = em.employee_id
where em.meal_item_id = mi.id
union all
select c.store_id, co.created, employee_id = null, co.customer_id
from customer_order_items coi
join customer_orders co on co.id = coi.customer_order_id
join customers c on c.id = co.customer_id
where coi.meal_item_id = mi.id) x
where x.store_id = 250 and x.created between '2021-02-03' and '2021-02-03'
order by mi.id
