Laravel 5.6中同时为子模型与孙模型应用查询条件的实现

嘿，我来帮你搞定这个查询需求～

首先咱们得先理清楚三张表的关联逻辑：Features通过field_id关联Fields的id，而Products和Features肯定存在外键关联（比如Features里有个product_id字段指向Products的id，这是把三张表串起来的关键）。

如果用原生SQL的话，咱们可以通过JOIN关联三张表，再加上筛选条件就能拿到目标记录：

-- 查询符合条件的Products完整信息
SELECT p.*
FROM Products p
JOIN Features f ON p.id = f.product_id
JOIN Fields fd ON f.field_id = fd.id
WHERE f.value_num = 2
AND fd.show_list = 1;

要是你想同时查看关联的Feature和Field详情，只需要调整SELECT的字段就行：

SELECT 
    p.id AS product_id, 
    f.id AS feature_id, 
    f.value_num, 
    fd.id AS field_id, 
    fd.show_list
FROM Products p
JOIN Features f ON p.id = f.product_id
JOIN Fields fd ON f.field_id = fd.id
WHERE f.value_num = 2
AND fd.show_list = 1;

先补全符合关联逻辑的模型定义（对应你的表结构）：

from django.db import models

class Products(models.Model):
    # 这里可以加你的其他字段，id是Django默认的自增主键
    class Meta:
        db_table = "Products"  # 指定对应数据库表名

class Fields(models.Model):
    show_list = models.IntegerField()
    
    class Meta:
        db_table = "Fields"

class Features(models.Model):
    value_num = models.IntegerField()
    field = models.ForeignKey(Fields, on_delete=models.CASCADE, db_column='field_id')
    product = models.ForeignKey(Products, on_delete=models.CASCADE, db_column='product_id')
    
    class Meta:
        db_table = "Features"

然后用ORM查询符合条件的Products：

# 获取所有满足条件的Products对象，用distinct避免同一个产品因多个符合条件的Feature重复出现
target_products = Products.objects.filter(
    features__value_num=2,
    features__field__show_list=1
).distinct()

如果需要一次性加载关联的Feature和Field数据来优化性能，可以用prefetch_related：

target_products_with_details = Products.objects.filter(
    features__value_num=2,
    features__field__show_list=1
).prefetch_related('features__field').distinct()

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