Laravel多对多关系下GROUP BY与OR条件的查询实现

需求说明 #

需要查询满足以下任一条件的商品（Articoli）：

• 指定供应商（fornitore_id）是该商品的唯一供应商
• 指定供应商是该商品的首选供应商（关联表中preferito字段值为"si"）

表结构 #

模型定义代码 #

商品模型（Articoli） #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Articoli extends Model
{
    protected $table = 'articoli';

    protected $fillable = [
        'cod_articolo',
        'descrizione',
        'quantita_minima',
        'note',
        'posizione',
        'prezzo_vendita',
    ];

    // 关联商品供应商关联表（一对多）
    public function articolo_fornitore()
    {
        return $this->hasMany(ArticoloFornitore::class);
    }
}

商品供应商关联模型（ArticoloFornitore） #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ArticoloFornitore extends Model
{
    protected $table = 'articolo_fornitore';

    protected $fillable = [
        'articolo_id',
        'fornitore_id',
        'cod_articolo_fornitore',
        'quantita_giacente',
        'preferito',
    ];

    // 关联商品表
    public function articolo()
    {
        return $this->belongsTo(Articoli::class, 'articolo_id');
    }

    // 关联供应商表
    public function fornitore()
    {
        return $this->belongsTo(Fornitore::class, 'fornitore_id');
    }
}

供应商模型（Fornitore） #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Fornitore extends Model
{
    protected $table = 'fornitori';

    protected $fillable = [
        'id',
        'rag_sociale',
        'pec',
        'sito',
        'p_iva',
        'cod_fiscale',
        'note',
        'mod_pagamento_id',
    ];

    // 关联商品供应商关联表（一对多）
    public function articolo_fornitore()
    {
        return $this->hasMany(ArticoloFornitore::class);
    }
}

已验证的原生SQL查询 #

SELECT 
    articoli.id, articoli.cod_articolo 
FROM 
    articoli 
INNER JOIN 
    articolo_fornitore on articoli.id = articolo_fornitore.articolo_id 
INNER JOIN 
    fornitori ON articolo_fornitore.fornitore_id = fornitori.id 
WHERE 
    -- 条件1：指定供应商是该商品的首选供应商
    (articolo_fornitore.fornitore_id = $idfornitore AND 
     articolo_fornitore.preferito = "si") 
    -- 条件2：指定供应商是该商品的唯一供应商
    OR (articolo_fornitore.fornitore_id = $idfornitore 
        AND articoli.id IN (SELECT articoli.id 
                            FROM articoli 
                            INNER JOIN articolo_fornitore ON articoli.id = articolo_fornitore.articolo_id 
                            GROUP BY articoli.id 
                            HAVING COUNT(DISTINCT articolo_fornitore.fornitore_id) = 1)) 

Laravel 适配实现 #

方式1：使用查询构造器（对应原生SQL） #

$idFornitore = 1; // 替换为实际的供应商ID

$articoli = DB::table('articoli')
    ->select('articoli.id', 'articoli.cod_articolo')
    ->join('articolo_fornitore', 'articoli.id', '=', 'articolo_fornitore.articolo_id')
    ->join('fornitori', 'articolo_fornitore.fornitore_id', '=', 'fornitori.id')
    ->where(function ($query) use ($idFornitore) {
        // 条件1：指定供应商是首选
        $query->where('articolo_fornitore.fornitore_id', $idFornitore)
              ->where('articolo_fornitore.preferito', 'si');
    })
    ->orWhere(function ($query) use ($idFornitore) {
        // 条件2：指定供应商是唯一供应商
        $query->where('articolo_fornitore.fornitore_id', $idFornitore)
              ->whereIn('articoli.id', function ($subquery) {
                  $subquery->select('articoli.id')
                           ->from('articoli')
                           ->join('articolo_fornitore', 'articoli.id', '=', 'articolo_fornitore.articolo_id')
                           ->groupBy('articoli.id')
                           ->havingRaw('COUNT(DISTINCT articolo_fornitore.fornitore_id) = 1');
              });
    })
    ->distinct() // 去重，避免同一商品因关联记录被多次查询
    ->get();

方式2：使用Eloquent模型关联查询 #

$idFornitore = 1;

$articoli = Articoli::whereHas('articolo_fornitore', function ($query) use ($idFornitore) {
    $query->where('fornitore_id', $idFornitore)
          ->where('preferito', 'si');
})
->orWhere(function ($query) use ($idFornitore) {
    // 先筛选出关联了指定供应商的商品
    $query->whereHas('articolo_fornitore', function ($q) use ($idFornitore) {
        $q->where('fornitore_id', $idFornitore);
    })
    // 再筛选出只有一个供应商的商品
    ->whereHas('articolo_fornitore', function ($q) {
        $q->groupBy('articolo_id')
          ->havingRaw('COUNT(DISTINCT fornitore_id) = 1');
    });
})
->select('id', 'cod_articolo')
->distinct()
->get();

补充：优化多对多关联定义 #

当前模型使用hasMany/belongsTo实现关联，更标准的多对多关系推荐用belongsToMany，可简化后续查询：
在Articoli模型中添加：

public function fornitori()
{
    return $this->belongsToMany(Fornitore::class, 'articolo_fornitore', 'articolo_id', 'fornitore_id')
                ->withPivot('preferito', 'cod_articolo_fornitore');
}

在Fornitore模型中添加：

public function articoli()
{
    return $this->belongsToMany(Articoli::class, 'articolo_fornitore', 'fornitore_id', 'articolo_id')
                ->withPivot('preferito', 'cod_articolo_fornitore');
}

优化后简化查询：

$idFornitore = 1;

$articoli = Articoli::whereHas('fornitori', function ($query) use ($idFornitore) {
    $query->where('fornitori.id', $idFornitore)
          ->where('articolo_fornitore.preferito', 'si');
})
->orWhere(function ($query) use ($idFornitore) {
    $query->whereHas('fornitori', function ($q) use ($idFornitore) {
        $q->where('id', $idFornitore);
    })
    ->whereHas('fornitori', function ($q) {
        $q->groupBy('articoli.id')
          ->havingRaw('COUNT(DISTINCT fornitori.id) = 1');
    });
})
->select('id', 'cod_articolo')
->distinct()
->get();

内容的提问来源于stack exchange，提问作者tonio_baldu