如何在Laravel中正确构建跨多级关联的分类筛选查询？

1. 原生SQL查询 #

假设各表关联字段如下：

• 商品表PRODUCTS通过category_id关联分类表CATEGORIES主键id
• 商品和门店多对多中间表为PRODUCT_STORE，关联字段为product_id、store_id
• 门店表STORES通过address_city_id关联城市地址表ADDRESS_CITY主键id
• 城市地址表ADDRESS_CITY用state字段存储州名

查询语句如下：

SELECT DISTINCT c.*
FROM CATEGORIES c
INNER JOIN PRODUCTS p ON c.id = p.category_id
INNER JOIN PRODUCT_STORE ps ON p.id = ps.product_id
INNER JOIN STORES s ON ps.store_id = s.id
INNER JOIN ADDRESS_CITY ac ON s.address_city_id = ac.id
WHERE ac.state = '加利福尼亚州';

用DISTINCT是为了避免同个分类下多个符合条件的商品导致分类结果重复。

2. Eloquent查询（Laravel框架场景） #

如果是用Laravel框架，不需要纠结hasManyThrough只能跨一张中间表的限制，直接用嵌套whereHas实现即可，首先要在各模型中提前定义好关联关系：

// Category 模型
public function products()
{
    return $this->hasMany(Product::class);
}

// Product 模型
public function stores()
{
    return $this->belongsToMany(Store::class);
}

// Store 模型
public function addressCity()
{
    return $this->belongsTo(AddressCity::class);
}

查询语句：

$californiaCategories = Category::whereHas('products', function ($q) {
    $q->whereHas('stores', function ($q) {
        $q->whereHas('addressCity', function ($q) {
            $q->where('state', '加利福尼亚州');
        });
    });
})->get();

这个写法自动满足你提的两个筛选条件：分类有至少一个关联商品，且该商品关联的门店地址位于加州。

你当前的数据规模（100万商品、1000门店）完全可以正常跑上述查询，不需要用JSON存储冗余字段的方案，理由如下：

• JSON字段的筛选、关联性能远不如结构化字段加索引的方案，而且冗余存储会带来数据一致性问题，比如城市ID变更时需要同步更新所有关联的JSON字段，维护成本极高。
• 只要给关联字段加对索引，查询效率可以达到毫秒级，需要添加的索引如下：
  • PRODUCTS表category_id加普通索引
  • 多对多中间表PRODUCT_STORE的product_id、store_id加联合索引
  • STORES表address_city_id加普通索引
  • ADDRESS_CITY表state加普通索引

如果后续数据量级继续增长，可以再加缓存、分表等优化，当前阶段完全不需要做冗余存储的改造。

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