mysql - Laravel Eager Loading 两端都有一些 where 条件

Question

我得到这样的 sql 查询:

select * from geofilter_activations where not exists (select * from snap_submissions where geofilter_activations.purchase_id = snap_submissions.purchase_id) and status = 0 and created_at < 2018-10-10 07:15:42 order by id desc limit 1

但是我添加的查询 wherenotnull of subscription_id 发生了什么？它不会出现在查询输出中。给我一种优化查询并使其工作的方法。

    $time_before = '10';
    $dt = Carbon::now();

    $activation = GeofilterActivation::doesntHave('submission_data')
        ->where('status', '0')
        ->where('created_at', '<', $dt->subMinutes($time_before)->toDateTimeString())
        ->OrderBy('id','desc')
        ->first();

    $activation->load(['purchase' => function ($query) {
        $query->whereNotNull('subscription_id');
    }]);

GeoFilterActivation 模型是:

<?php

namespace App\Models\Geofilter;

use Illuminate\Database\Eloquent\Model;

class GeofilterActivation extends Model
{
    public function purchase(){
        return $this->belongsTo('App\Models\Geofilter\GeofilterPurchase', 'purchase_id');
    }
    public function submission_data(){
        return $this->belongsTo('App\Models\Geofilter\SnapSubmission', 'purchase_id', 'purchase_id');
    }

    public function ad_stat(){
        return $this->belongsTo('App\Models\Geofilter\SnapAdStat', 'purchase_id', 'purchase_id');
    }

    public function currency(){
        return $this->belongsTo('App\Models\Currency', 'currency_code', 'code');
    }
}

Answer 1

也许这些方面的东西可以帮助您:

$result = GeofilterActivation::with(['purchase' => function($query) {
        $query->whereNotNull('subscription_id');
    }])
    ->doesntHave('submission_data')
    ->where('status','0')
    ->where('created_at', '<', $dt->subMinutes($time_before)->toDateTimeString())
    ->OrderBy('id','desc')
    ->first();

您的查询不包含 load 的原因是因为 load 是在您的查询执行后调用的，因此您必须在查询中包含使用的关系与。您在上面提到的加载示例实际上是延迟加载，因为您在需要时请求对象。