CakePHP - Creating a Complex Request

Question

I have the following query that I want to build using CakePHP. How can I do it?

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 the model is Artist, and it has a relationship with, then you can use ORM CakePHP in this way and hasMany with Portfolio

first you change the relationship between Artist and Portfolio.

$this->Artist->unbindModel(array('hasMany'=>array('Portfolio'))) ;

and then build a relationship

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

Finally, you must create another relationship

 $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 that the relationship is fulfilled, you can make 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 this can be useful for you.

Answer 2

You have to put it in your model. If you have not read it, I suggest you read about skinny controllers and thick 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

You can use the query method, but this method is considered as a last resort when find or any of the other convenience methods are missing.

But you can also manually specify the associations in the Cake find method, see the cooking . I'm not quite sure, but I believe that nested connections are also supported. CONCAT_WS can only be called with the appropriate fields in the field property of the find method.

Answer 4

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

Instead, you should try to make this request as close to metal as possible. I am not familiar with the CakePHP API (as I try to avoid it like a black plague), but you should be able to create a Model that is not related to ActiveRecord, and most likely has access to something like this PDO shell. Use this to execute a query and map the results to variables.

---

However, you can examine your request (you seem to have some kind of compulsive quote). One of the improvements you could make would be to stop using id columns in tables.

All of this setup can really benefit from creating another column in the Artists table: portfolio_size . Which you can update every time you need to change it. Yes, this will de-normalize your table, but it will also make this query trivial and fast, with little cost elsewhere.

---

As with column names, if the Artists table has an identifier, then save it in the artist_id column, and if Products has a foreign key that references Artists.artist_id , then also name it artist_id . The same should have the same name throughout your database. It will also allow you to use USING in SQL. 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 guess this query needs no explanation, as it is a simple many-to-many relationship between users and groups.