CakePHP – 构建复杂查询

发布时间：2020-12-13 22:32:50 所属栏目：PHP教程来源：网络整理

导读：我想使用Cake PHP构建以下查询.我该怎么办呢？ 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 O

我想使用Cake PHP构建以下查询.我该怎么办呢？

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`;

解决方法

我认为该模型是Artist并且它与belongsTo有关系,那么你可以用这种方式使用CakePHP ORM并且hasMany with Portfolio

首先,你要破坏艺术家和投资组合之间的关系

$这 – > Artist-> unbindModel(阵列( ‘的hasMany’=>阵列( ‘组合’)));

然后建立关系

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

最后,您必须创建其他关系

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

现在,当关系完成后,你可以找到你的发现

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

我希望它对你有用

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!