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

Question

我目前正在尝试在我的 CakePHP 站点中实现搜索引擎功能，试图有效地从 3 个表中返回信息。主要用途是数字搜索，自由文本将非常少，因此我不会尝试针对这种情况进行优化。

我遇到的问题是尝试对一个表中的结果进行分组以减少重复信息，很抱歉发了这么长的帖子!

正在使用的表如下:

Companies hasMany Products
Products hasMany Prices

我有一个成功的方法，可以使用下面的代码(作为问题 here 的结果)根据条件从所有 3 个表返回结果

    //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
                    )
            )
    )
)

问题:如何修改上述代码以从 Price 表返回包含分组结果的信息以减少重复，但仅返回那些实际满足指定条件的信息？

奖励: 如果有更有效的方法来执行上述搜索，我将非常想知道。尽管以上花费了 0 毫秒，我在本地机器上得到的结果数量有限，但 CakePHP 仍然告诉我“可能很慢”，我认为这是连接的结果。

Answer 1

将问题一分为二

您描述的是:

• 查找所有至少一个符合条件的产品
• 对于这些产品，返回具有匹配价格数据的产品。

您描述的关联是:

Company hasMany Product 
Product hasMany Price

或者:

Product belongsTo Company
Price belongsTo Product

这样表达可能很明显，如果递归为 0 或更大，则对 Product 的查找将加入 Company。这删除了一个手动连接。

确保退回正确的产品

首先确保您获得了所需的产品列表。根据描述，可以选择使用连接进行设置:

$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
);

请注意，现在在主查找/分页调用中没有额外的连接。

在这两个示例中，应该执行一个查询(加上计数)，没有任何价格数据。

使用 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);

    ...
}

这应该会产生两个 查询(加上一个计数)，以及您要查找的数据结构；包括价格数据。

可能比较慢

Even though the above takes 0ms, [...] CakePHP is still telling me "maybe slow"

调试工具包不询问数据库，以确定查询是否“可能很慢”它是 simple test对于:

• 查询耗时超过 0ms
• 查询每个结果花费的时间超过 1 毫秒
• 查询花费的时间超过阈值(默认为 20 毫秒)

从检查代码来看，它永远不应该将 0 毫秒查询标记为“可能很慢”——但如果是这样也不是问题。

对于所有数据库事件，最好在数据库上运行解释、添加任何缺失的索引并考虑将返回相同数据的不同查询结构。