Sometimes, you want to use join or left join or right join, etc … in eloquent, here is one example
$orders = $this->order
->select(['orders.*'])
->join('customers', 'customers.id', '=', 'orders.customer_id')
->leftJoin('logs', function($join) {
$join->on('orders.id', '=', 'logs.entity_id');
$join->where('logs.name', '=', EventsLog::EVENT_EMAIL_SENT_ORDER_PAYMENT_REMINDER);
$join->where('logs.entity_type', '=', 'order');
})
->where('orders.status', Order::STATUS_CREATED)
->where('orders.created_at', '<=', Carbon::now()->subDay($days))
->whereNull('logs.entity_id')
->get();
return $orders;
This will be same as raw sql query (postgres):
select orders.id, customers.firstname, customers.lastname, logs.entity_id
from orders
join customers on (orders.customer_id = customers.id)
left join logs on (orders.id = logs.entity_id AND logs.entity_type = 'order' AND logs.name = 'email_sent_order_payment_reminder')
where orders.status = 'created'
and orders.created_at <= (CURRENT_DATE - INTERVAL '3 day')::date
and logs.entity_id is null
Hope this will save your time