Shane Shane - 5 months ago 47
SQL Question

Laravel 4.2 - Select only rows where count of rows based on another query are 0

I want to set up scheduled emails for recommended oil changes. To do this, I need to select from a table of line items where the last oil change was over 3 months ago. I need a condition to ignore currently selected customer rows where an oil change was purchased under 3 months ago. How would I add this condition?

$search_term = 'oil change';
$date = new DateTime('-3 months');

$users = $this->prospectInstance->getDatabase()->table('LineItems')
->join('WorkOrder', 'WorkOrder.id', '=', 'LineItems.order_id')
->join('Customer', 'Customer.customer_id', '=', 'WorkOrder.customer_id')
->where('LineItems.line_type', 'like', "%$search_term%")
->where('WorkOrder.create_date', '<=', $date)
// this block produces errors
->whereNotIn('Customer.customer_id', function($query) use ($search_term, $date)
{
return $query->where('LineItems.line_type', 'like', "%$search_term%")
->join('WorkOrder', 'WorkOrder.id', '=', 'LineItems.order_id')
->join('Customer', 'Customer.customer_id', '=', 'WorkOrder.customer_id')
->where('WorkOrder.create_date', '>=', $date);
})
->orderBy('WorkOrder.create_date', 'DESC')
->groupBy('Customer.customer_id');


Table Structure:

LineItems
order_id
line_type

WorkOrder
id
customer_id
create_date

Customer
customer_id


Edit: I was able to use the advice below to use two separate queries to accomplish this, but I'd like to know if there is a single query I can create to accomplish the same result:

$search_term = 'oil change';
$date = new DateTime('-3 months');

$base_query = $this->prospectInstance->getDatabase()->table('LineItems')
->join('WorkOrder', 'WorkOrder.id', '=', 'LineItems.order_id')
->join('Customer', 'Customer.customer_id', '=', 'WorkOrder.customer_id')
->where('LineItems.line_type', 'like', "%$search_term%")
->orderBy('WorkOrder.create_date', 'desc')
->groupBy('Customer.customer_id');

$recent = clone $base_query;
$users = clone $base_query;

$recent->where('WorkOrder.create_date', '>', $date)
->select('Customer.customer_id');
$users->where('WorkOrder.create_date', '<=', $date)
->whereNotIn('Customer.customer_id', $recent->lists('customer_id'));

Answer

I think this would work but is obviously not tested:

$users = $this->prospectInstance->getDatabase()->table('Customer')
    ->join('WorkOrder', 'WorkOrder.customer_id', '=', 'Customer.customer_id')
    ->join('LineItems', 'LineItems.order_id', '=', 'WorkOrder.id')
    ->where('LineItems.line_type', 'like', "%$search_term%")
    ->whereNotIn('Customer.customer_id', function($query) use ($date, $search_term)
    {
        $query->select('Customer.customer_id')
            ->from('Customer')
            ->join('WorkOrder', 'Customer.customer_id', '=', 'WorkOrder.customer_id')
            ->join('LineItems', 'WorkOrder.id', '=', 'LineItems.order_id')
            ->where('WorkOrder.create_date', '>', $date)
            ->where('LineItems.line_type', 'like', "%$search_term%");
    })
    ->orderBy('WorkOrder.create_date', 'desc')
    ->groupBy('Customer.customer_id');