laravel - 如何在 Laravel 中使用 join 查找 DB 语句中的关系缺失

Question

我正在构建一个应用程序，我试图获取在 join语句Laravel 8.0中不存在的关系。DB我没有得到想要的结果

我尝试使用

    public function ProductList() {
      $innerQuery = DB::table('projects as p')
        ->join('construction_product_projects as cpp', 'p.id', '=', 'cpp.project_id')
        ->join('construction_product as cp', 'cpp.construction_product_id', '=', 'cp.id')
        ->where('cp.status', 'saved')
        ->join('project_associate_brand as pab', function ($join){
            $join->on('cp.id', '=', 'pab.construction_product_id')
                ->when(request('group'), function ($q) {
                    $q->whereIn('pab.product_group_id', collect(request('group'))->pluck('id'));
                })
                ->when(request('brand'), function ($q) {
                    $q->whereIn('pab.brand_id', collect(request('brand'))->pluck('id'))
                        ->when(request('equivalent'), function ($q){
                            $q->where('pab.equivalent',true); //where project_associate_brand.equivalent is true
                        });
                })
                ->when(request('brand_doesnt_have'), function ($q) {
                
                    // This is not working...
                
                    $q->whereNotIn('pab.brand_id', collect(request('brand_doesnt_have'))->pluck('id'));
                    
                });
        })
        ->leftjoin('Construction_product_attachments as cpa', 'cpa.construction_product_id', '=', 'cp.id')

        ->select(DB::raw("p.id, p.slug, p.name, cp.updated_at"))
        ->whereNull('p.deleted_at')
        ->whereNull('cp.deleted_at')
        ->whereNull('cpa.deleted_at')
        ->groupByRaw('p.id');

    return ProductListResource::collection(
            DB::query()->fromSub($innerQuery, 't')
                ->select(DB::raw("id, slug, name, updated_at, attachments"))
                ->groupBy('t.id')
                ->paginate(50)
        );
}

所以这不起作用所以我尝试使用whereExists和whereNotIn条件：

$q->whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('project_associate_brand')
        ->whereNotIn('project_associate_brand.brand_id', collect(request('brand_doesnt_have'))->pluck('id'));
});

欢迎任何更好的方法。谢谢。

PS：我也尝试使用该功能whereNotExists但不起作用。我不能使用Eloquent，whereDoesntHave因为我在其中执行了很多复杂的查询/条件。

编辑：

toSql()我尝试在得到以下输出的帮助下生成查询生成器：

select p.id, p.slug, p.name, cp.updated_at from projectsas pinner join construction_product_projectsas cppon p。id= cpp。project_id内部连接construction_product为cpon cpp。construction_product_id= cp。id内部连接project_associate_brand为pabon cp。id= pab。construction_product_id和pab。brand_id不在 (?) 哪里p。deleted_at为空且cp. deleted_at按 p.id 为空组

但这不是预期的。我的期望如下：

select * from projectswhere not exists (select * from brandsinner join project_associate_brandon brands. id= project_associate_brand. brand_idwhere projects. id= project_associate_brand. project_idand project_associate_brand. brand_idnot in (?) and brands. deleted_atis null) and projects. deleted_at一片空白

查询应该具有where not exists我的代码中不存在的内容。

标签： laraveljoineloquentlaravel-query-builder

Answer 1

您需要在此处使用 whereDoesntHave 以在查询中具有等效的 where not exists 子句。您期望的简单表示是：

Projects::whereDoesntHave('project_associate_brand', function($query) {
    $query->join('brands', function($join) {
        $join->on('brands.id','=','project_associate_brand.brand_id')
            ->whereNull('brands.deleted_at');       

    });
    $query->whereNotIn('project_associate_brand.brand_id',[?])
})