查询关联有效且存在types记录的categories与products数据

表结构 #

categories (id, categoryName)
products (id, categoryID, productName)
types (id, productID, typeName, active) -- active为布尔类型

方案一：使用INNER JOIN + 分组过滤 #

直接关联types表并过滤有效记录，通过分组确保产品存在关联的有效类型数据：

Categories::find()
    ->select('categoryName, productName, products.id')
    ->orderBy('categoryName, productName ASC')
    ->joinWith([
        'products' => function ($query) {
            $query->innerJoin('types', 'types.productID = products.id')
                  ->where(['types.active' => 1])
                  ->groupBy('products.id')
                  ->having('COUNT(types.id) > 0');
        }
    ])
    ->asArray()
    ->all();

方案二：使用EXISTS子查询（推荐） #

通过EXISTS子查询判断当前产品是否存在有效类型记录，大数据量场景下性能更优：

Categories::find()
    ->select('categoryName, productName, products.id')
    ->orderBy('categoryName, productName ASC')
    ->joinWith([
        'products' => function ($query) {
            $query->where(['exists', 
                (new \yii\db\Query())
                    ->select('1')
                    ->from('types')
                    ->where([
                        'types.productID' => new \yii\db\Expression('products.id'),
                        'types.active' => 1
                    ])
            ]);
        }
    ])
    ->asArray()
    ->all();

关键说明 #

• 两种方案都通过joinWith的回调函数对关联的products添加过滤逻辑，仅保留存在至少一条active=1的types记录的产品
• 方案二的EXISTS子查询会在找到第一条匹配记录后停止检索，比分组统计的执行效率更高
• 最终结果仅返回符合条件的分类与产品关联数据

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