CakePHP - Building a Complex Query

Question

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

Answer 1

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

and then Build the relations

$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', 
)
))

I hope it could be useful for you

Answer 2

You should place it in your model. If you haven't read yet, I suggest you to read about skinny controllers and fat models.

class YourModel extends AppModel {

    public function getArtistsAndPortfolioCounts() {
        return $this->query("SELECT ... ");
    }

}

So in your controller:

class ArtistsControllre extends AppController {
    public function yourAction() {
        debug($this->YourModel->getArtistsAndPortfolioCounts());
    }
}

Answer 3

Do not try to build that query using ORM. It will be a disaster in slow motion.

Instead you should try to do that request as "close to metal" as possible. I am not familiar with CakePHP's API ( as i tend to avoid it like black plague ), but you should be able to create a Model which is not related to ActiveRecord, and most likely have access to anything similar to PDO wrapper. Use that to execute the query and map results to variables.

---

That said, you might want to examine your query ( you seem to have some compulsive quoting sickness ). One of improvements you could do would be stop using id columns in tables.

This whole setup could really benefit from creation of another column in Artists table : portfolio_size. Which you can update each time it should be changed. Yes , it would de-normalize your table, but it also will make this query trivial and blazing fast, with minor costs elsewhere.

---

As for names of columns, if table Artists has a id, then keep it in column artist_id , and if Products has a foreign key referring to Artists.artist_id then name it too artist_id. Same thing should have same name all over your database. This would additionally let you use in SQL USING statement. Here is a small example :

SELECT 
   Users.name
   Users.email
FROM Users
LEFT JOIN GroupUsers USING (user_id)
LEFT JOIN Groups USING (group_id)
WHERE Groups.name = 'wheel'

I assume that this query does not need explanation as it is a simple many-to-many relationship between users and groups.

Answer 4

You could use the query method, but that method is treated as a last resort, when find or any of the other convenience methods don't suffice.

But it's also possible to manually specify joins in the Cake find method, see the cookbook. I'm not entirely sure, but I believe nested joins are also supported. CONCAT_WS could just be called, with the relevant fields, in the field property of the find method.