CakePHP-使用JOIN有效地搜索3个表 [英] CakePHP - Searching 3 tables efficiently using JOIN

问题描述

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

I'm currently trying to implement a search engine function in my CakePHP site, trying to return information from 3 tables efficiently. The main usage will be numeric searches, free text will be extremely minimal and as such I'm not trying to optimise for this scenario.

Companies hasMany Products
Products hasMany Prices

理想情况下，我希望能够在Products控制器中使用类似以下功能的东西（由于关系太远而无法使用）：

Ideally I'd like to be able to use something like the following function in the Products controller (this won't work due to distant relationships):

$results = $this->Product->find('all', array(
    //conditions can be defined against all 3 tables
    'conditions' =>. array(
        'company.name LIKE' => '%'.$search_term.'%',
        'product.feature' => $product_feature,
        'price.price <' => $price
    ),
    //fields restricted against all 3 tables
    'fields' => array(
        'company.name',
        'product.feature',
        'price.price'
    )
));

我尝试使用可包含行为包括三个模型，但无济于事。

I've tried using Containable behaviour to include the three models but to no avail.

我相信解决方案取决于JOINS，但我的经验有限，我在上面的find函数中尝试了类似于以下代码的代码：

I believe the solution lies with JOINS but my experience with these is limited, I've tried code similar to the following within the find function above:

'joins' => array(
    array(
        'table' => 'companies',
        'alias' => 'Company',
        //tried a mix of joins (LEFT, RIGHT, INNER)
        'type' => 'LEFT',
        'conditions' => array(
            'Company.id = Product.company_id'
        )
    ),
    array(
        'table' => 'prices',
        'alias' => 'Price',
        //tried a mix of joins (LEFT, RIGHT, INNER)
        'type' => 'LEFT',
        'conditions' => array(
            'Price.product_id = Product.id'
        )
    ),
),
'recursive' => 1,

编辑：以上联接的结果是，当我在条件中指定价格或找不到的字段，我尝试更改名称（例如Product.Price.price）以解决一对多关系，但仍然没有运气。

The result of the above joins is that when i specify the price in the conditions or fields that it can't be found, I tried changing the names such as Product.Price.price to account for the one to many relationship but still without luck.

I感谢您在寻求解决方案方面的帮助！

I'd appreciate any help in finding a solution!

推荐答案

我更喜欢使用蛋糕模型/的代码表命名约定（数据库表产品-型号名称产品，数据库表价格-型号名称价格）以进行进一步的项目管理。看来您想执行以下操作：

I prefer less code with cake model/table naming convention (db table products - model name Product, db table prices - model name Price) for further project management. It's looks like You want to do:

$results = $this->Product->find('all', array(
    'fields' => array(
        'Company.name',
        'Product.feature',
        'Price.price'
    ),
    'joins' => array(
        'LEFT JOIN companies AS Company ON Product.company_id = Company.id
         LEFT JOIN prices AS Price ON Product.id = Price.product_id'
    ),
    'conditions' => array(
        'Company.name LIKE' => '%'.$search_term.'%',
        'Product.feature' => $product_feature,
        'Price.price <' => $price
    ),

 ));

但是如果，您想获得产品 >您的所有条件（公司和价格）仅 ，您应使用 INNER JOIN 和 GROUP BY 产品（ group 选项）。

but if You want to get products with Your all criteria (company and price) only, You should use INNER JOIN, and GROUP BY Product (group option).

此外，如果要获得所有具有许多价格和公司业绩的产品，并设置/链接模型关系，可以使用包含选项，例如：

Also, if You want to get all products with many prices and companies result and You set/link model relations, You can use contain option, like:

$contain = array(
    'Company' => array(
        // ...
        'conditions' => array('Company.name LIKE' => '%'.$search_term.'%'),
        // ...
    ),
    'Price' => array(
        // you can set: 'fields' => array('id', ...),
        'conditions' => array('Price.price <' => $price),
        // you can set order: 'ordder' => '....'                
    )
);

$this->Product->attach('Containable');
$post = $this->Product->find('all', array(
    // ...
    'contain' => $contain,
    'conditions' => array('Product.feature' => $product_feature),
    // ...
)); 

因此，您将获得具有 feature => $ product_feautre ，您将获得左加盟公司和此产品的价格。

So You will get all products with feature => $product_feautre, and You get LEFT JOIN companies and prices to this products.

希望这会有所帮助。

这篇关于CakePHP-使用JOIN有效地搜索3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！