CODEWITHSUNDEEP
phpmysqllaravellaravel-4
Shane
Shane

Reputation: 1230

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'));

Upvotes: 0

Views: 142

Answers (1)

Alex Harris
Alex Harris

Reputation: 6392

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');

Upvotes: 1

Related Questions