php - CakePHP-对JOIN进行分组和重复数据删除

我目前正试图在我的CakePHP站点中实现一个搜索引擎功能，试图高效地返回3个表中的信息。主要用途将是数字搜索，自由文本将是非常少的，因此我不试图优化这个场景。
我遇到的问题是试图将一个表中的结果分组，以减少重复信息，对不起，这篇长文章！
使用的表格如下：

Companies hasMany Products
Products hasMany Prices

我有一个成功的方法，使用下面的代码，根据任何或所有表的条件返回所有3个表的结果（结果是问题here）

    //configure search conditions
    $options['conditions'] = array(
        'Company.name LIKE' => '%'.$search_term.'%',
        'Product.feature' => $product_feature,
        'Price.price <' => $price
    );

    //configure search fields
    $options['fields'] = array(
        'Company.id',
        'Company.name',
        'Product.id',
        'Product.feature',
        'Price.id',
        'Price.price',
    );

    //configure search joins
    $options['joins'] = array(
        'INNER JOIN prices as Price ON Price.product_id = Product.id INNER JOIN companies as Company ON Product.company_id = Company.id'
    );

    //configure recursion
    $options['recursive'] = -1;

    //configure pagination options
    $this->Paginator->settings = $options;

    //retrieve results and pass to view
    $this->set('results', $this->Paginator->paginate('Product'));

上述查询返回的结果如下：

Array
(
[0] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [name] => Company 1
            )

        [Product] => Array
            (
                [id] => 1
                [feature] => true
            )

        [Price] => Array
            (
                [id] => 1
                [price] => 1.00
            )

    )

[1] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [name] => Company 1
            )

        [Product] => Array
            (
                [id] => 1
                [feature] => true
            )

        [Price] => Array
            (
                [id] => 2
                [price] => 2.00
            )

    )
)

如您所见，上述实例中的公司和产品信息是重复的，理想情况下，我希望信息返回如下：

Array
(
[0] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [name] => Company 1
            )

        [Product] => Array
            (
                [id] => 1
                [feature] => true
            )

        [Price] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [price] => 1.00
                    )
                [1] => Array
                    (
                        [id] => 2
                        [price] => 2.00
                    )
            )
    )
)

我通过使用以下设置创建了此项：

    //configure search joins
    $options['joins'] = array(
        'INNER JOIN prices as Price ON Price.product_id = Product.id'
    );

    //configure recursion
    $options['recursive'] = 1;

上述方法继续只返回满足公司和产品所有条件的结果，但在价格数组中，它返回指定公司和产品的所有价格，而不仅仅是满足条件的价格。
例如：“最高价格为1”的条件加上上述信息，将返回所有价格满足“最高价格为1”条件的公司和产品，问题是它将列出所有价格，即使是不符合条件的价格，如下所示：

Array
(
[0] => Array
    (
        [Company] => Array
            (
                [id] => 1
                [name] => Company 1
            )

        [Product] => Array
            (
                [id] => 1
                [feature] => true
            )

        [Price] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [price] => 1.00
                    )
                //the below array result shouldn't be here as it doesn't meet the condition "max price of 1"
                [1] => Array
                    (
                        [id] => 2
                        [price] => 2.00
                    )
            )
    )
)

问题：如何修改上述代码以返回价格表中包含分组结果的信息以减少重复，但仅返回那些实际满足指定条件的信息？
奖励：如果有更有效的方法来执行上述搜索，我将非常感兴趣知道。尽管上面花了0毫秒，但我在本地机器上得到的结果有限，CakePHP仍然告诉我“可能很慢”，我相信这是连接的结果。

最佳答案

把问题一分为二
你所描述的是：
查找至少有一个产品符合条件的所有产品
对于这些产品，返回具有匹配价格数据的产品。
您描述的关联是：

Company hasMany Product
Product hasMany Price

或：

Product belongsTo Company
Price belongsTo Product

如果递归为0或更大，则很明显，产品上的查找将加入公司。移除一个手动连接。
确保正确的产品被退回
首先确保获得所需产品的列表。根据描述，可以选择使用连接设置：

$options['recursive'] = 0; // temporary

$options['conditions'] = array(
    'Company.name LIKE' => '%'.$search_term.'%',
    'Product.feature' => $product_feature,
    'Price.price <' => $price
);

//configure search fields
$options['fields'] = array(
    'Distinct Product.id',
    'Product.feature',
    'Company.id',
    'Company.name',
    #'Price.id', No
    #'Price.price', No
);

$options['joins'][] = 'INNER JOIN prices as PriceFilter ON Price.product_id = Product.id';

或条件：

$options['recursive'] = 0; // temporary

$options['conditions'] = array(
    'Company.name LIKE' => '%'.$search_term.'%',
    'Product.feature' => $product_feature,
    "WHERE EXISTS (select * from prices where prices.product_id = Product.id AND prices.price < $price)"
);

$options['fields'] = array(
    'Product.id',
    'Product.feature',
    'Company.id',
    'Company.name',
    #'Price.id', No
    #'Price.price', No
);

注意，现在主find/paginate调用中没有额外的连接。
在这两个示例中，应该执行一个查询（加上一个计数），而不执行任何价格数据。
使用containable获取匹配的价格
Containable使管理执行的查询和返回的结果范围变得更容易。在这种情况下，所需的只是将价格数据添加到结果集-并过滤价格。演示使用contain选项的完整示例：

public $paginate = array(
    'contain' => array(
        'Company',
        'Price' => array()
    ),
    'fields' => array(
        'Product.id',
        'Product.feature',
        'Company.id',
        'Company.name'
    )
);

function whatever() {
    ...

    $this->paginate['contain']['Price']['conditions']['Price.price <'] = $price;

    $conditions = array(
        'Company.name LIKE' => '%'.$search_term.'%',
        'Product.feature' => $product_feature,
        "WHERE EXISTS (select * from prices where prices.product_id = Product.id AND prices.price < $price)"
    );

    $result = $this->paginate('Product', $conditions);

    ...
}

这将导致两个查询（加上一个计数），以及您要查找的数据结构；包括价格数据。
也许慢一点
尽管上面需要0毫秒，[…]CakePHP仍然告诉我“可能很慢”
调试工具包没有对数据库进行互操作，以确定查询是否“可能很慢”，它是一个simple test用于：
查询时间超过0毫秒
查询每个结果花费的时间超过1毫秒
查询时间超过threshold（默认为20毫秒）
从检查代码开始，它就不应该将一个0ms查询标记为“可能很慢”，但如果这样做了就不会有问题。
与所有数据库活动一样，最好在数据库上运行explain，添加任何缺少的索引，并考虑返回相同数据的不同查询结构。

关于php - CakePHP-对JOIN进行分组和重复数据删除，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23375649/