CakePHP 3 中的子查询? [英] Subqueries in CakePHP 3?

问题描述

我有两个表 products 和 product_categories，它们通过第三个表 products_categories_products 关联，根据 CakePHP BelongsToMany 约定(这些在 ProductsTable.php 和 ProductCategoriesTable.php 中建立关联.我想生成一个产品类别列表，使用最畅销产品的图片来代表每个类别.

I have two tables products and product_categories that are associated through a third table, products_categories_products, according to CakePHP BelongsToMany conventions ( these associations are established in ProductsTable.php and ProductCategoriesTable.php). I want to generate a list of product categories, using the image from the best selling products to represent each category.

我可以使用以下功能实现我想要的结果:

I can achieve my desired outcome using the following function:

public function findImages(Query $query, array $options) {
    $query->select([
        'ProductCategories.id',
        'ProductCategories.name',
        'ProductCategories__image' => '(SELECT Products.image
        FROM product_categories_products AS ProductCategoriesProducts
        LEFT JOIN products AS Products
        ON ProductCategoriesProducts.product_id = Products.id
        WHERE ProductCategoriesProducts.product_category_id = ProductCategories.id
        ORDER BY Products.turnover DESC
        LIMIT 1)'
    ])->where([
        'ProductCategories.name <>' => 'Unsorted'
    ])->order([
        'ProductCategories.name'    => 'asc'
    ]);
    return $query;
}

这是可以接受的还是有更简单的方法来实现我的目标?我真的找不到关于在 CakePHP 3 中制作子查询的任何内容.经过几个小时的挫折后，我偶然发现了上述解决方案.任何建议表示赞赏！

Is this acceptable or is there a Cake-ier way to achieve my goal? I couldn't really find anything on the topic of crafting subqueries in CakePHP 3. I just stumbled into the above solution after a couple hours of frustration. Any advice appreciated!

推荐答案

感谢用户 ndm_yesterday 的链接，我生成了以下解决方案:

Thanks to the link from user ndm_yesterday, I generated the following solution:

public function findImages(Query $query, array $options) {
    $this->hasMany('ProductCategoriesProducts');

    $subquery = $this->ProductCategoriesProducts->find('all')->select([
        'Products.image'
    ])->join([
        [
            'table'     => 'products',
            'alias'     => 'Products',
            'type'      => 'LEFT',
            'conditions'=> [
                'ProductCategoriesProducts.product_id = Products.id'
            ]
        ]
    ])->where([
        'ProductCategoriesProducts.product_category_id = ProductCategories.id'
    ])->order([
        'Products.turnover' => 'DESC'
    ])->limit(1);

    $query->select([
        'ProductCategories.id',
        'ProductCategories.name',
        'ProductCategories__image' => $subquery
    ])->where([
        'ProductCategories.name <>' => 'Unsorted'
    ])->order([
        'ProductCategories.name'    => 'asc'
    ]);

    return $query;
}

这篇关于CakePHP 3 中的子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！