php - Laravel 4.2 - 仅选择基于另一个查询的行数为 0 的行

Question

我想设置定期电子邮件以推荐换油。为此，我需要从最后一次换油是在 3 个月前的行项目表中进行选择。我需要一个条件来忽略当前选定的客户行，其中在 3 个月前购买了换油。我该如何添加这个条件？

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

表结构:

LineItems
    order_id
    line_type

WorkOrder
    id
    customer_id
    create_date

Customer
    customer_id

编辑:我能够使用下面的建议来使用两个单独的查询来完成此操作，但我想知道是否可以创建一个查询来完成相同的结果:

$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 1

我认为这可行，但显然没有经过测试:

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