CakePHP - 构建复杂查询 [英] CakePHP - Building a Complex Query

问题描述

我有以下的查询我想使用CakePHP构建。我应该怎么做呢？

I have the following query I want to build using CakePHP. How should I go about this?

        SELECT
            `Artist`.`id`,
            CONCAT_WS(' ', `Person`.`first_name`, `Person`.`last_name`, `Person`.`post_nominal_letters`) AS `name`,
            `Portfolio`.`count`
        FROM
            `people` as `Person`,
            `artists` as `Artist`
        LEFT OUTER JOIN
            (SELECT
                `Product`.`artist_id`,
                 COUNT(DISTINCT `Product`.`id`) AS `count`
            FROM
                `product_availabilities` AS `ProductAvailability`,
                `products` AS `Product`
            LEFT OUTER JOIN
                `order_details` AS `OrderDetail`
            ON
                `Product`.`id` = `OrderDetail`.`product_id`
            LEFT OUTER JOIN
                `orders` AS `Order`
            ON
                `Order`.`id` = `OrderDetail`.`order_id`
            WHERE
                `ProductAvailability`.`id` = `Product`.`product_availability_id`
            AND
                `Product`.`online` = true
            AND
                (`ProductAvailability`.`name` = 'For sale')
                OR
                    ((`ProductAvailability`.`name` = 'Sold') AND (DATEDIFF(now(),`Order`.`order_date`) <= 30))
            GROUP BY
                `Product`.`artist_id`)
        AS
            `Portfolio`
        ON
            `Artist`.`id` = `Portfolio`.`artist_id`
        WHERE
            `Artist`.`person_id` = `Person`.`id`
        AND
            `Artist`.`online` = true
        GROUP BY
            `Artist`.`id`
        ORDER BY
            `Person`.`last_name`, `Person`.`first_name`;

推荐答案

我认为模型是艺术家， belongsTo与您的关系，那么您可以在这种方式使用CakePHP ORM和hasMany与投资组合

I think that the model is Artist and It has a belongsTo Relationship with , then you could use the CakePHP ORM on this way and hasMany with Portfolio

首先你要分配艺术家和投资组合之间的关系

first you mus distroy the relation between Artist and Portfolio

$ this-> Artist-> unbindModel（array（'hasMany'=> array（'Portfolio'）））;

然后建立关系

$this->Artist->bindModel(array('hasOne'=>array('Portfolio')));

最后，您必须创建其他关系。

Finally you must create the other relationsships

$this->Artist->bindModel(array(
'belongsTo'=>array(
'Product'=>array(
 'clasName'=>'Product',
 'foreignKey'=> false,
 'conditions'=>'Product.id = Artist.product_id'
 ),
 'ProductAvaibility'=>array(
 'clasName'=>'ProductAvaibility',
 'foreignKey'=> false,
 'conditions'=>'ProductAvaibility.id = Product.product_avaibility_id'
 ),
 'OrderDetail'=>array(
 'clasName'=>'OrderDetail',
 'foreignKey'=> false,
 'conditions'=>'Product.id = OrderDetail.product_id'
 ),
 'Order'=>array(
 'clasName'=>'Order',
 'foreignKey'=> false,
 'conditions'=>'Order.id = OrderDetail.order_id'
 ),
)
));

现在，当关系完成后，你可以找到

Now, when the relationships are done, you could do your find

$this->Artist->find('all', array(
'conditions'=>array(
'Artist.online'=>true
),
'group'=>array(
'Artist.id'
),
'order'=>array(
'Person.last_name', 
'Person.first_name', 
)
))

我希望它对您有用

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