Multi level ordering in cakePHP

Question

Why don't cakePHP include all my associations into one SQL query? The only way I have been able to do this is using "joins", but I hoped belongsTo and Containable behaviour was enough.

Here is an example: Post->belongsTo->Category->belongsTo->category_type (All models are setup correctly and work.

listing posts with pagination in index, I try this:

public function index() {   
    $this->paginate = array( 'contain' => array('Category' => array('CategoryType')));
    $this->set('posts', $this->paginate());
}

This fetches the array correctly, but it does it in many SQLs like this:

SELECT `Post`.`id`, `Post`.`name`, `Post`.`content`, `Post`.`category_id`, `Category`.`id`, `Category`.`name`, `Category`.`category_type_id` FROM `unit_app`.`post` AS `Post` LEFT JOIN `unit_app`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) WHERE 1 = 1 LIMIT 20
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 1
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2
SELECT `CategoryType`.`id`, `CategoryType`.`name` FROM `unit_app`.`category_types` AS `CategoryType` WHERE `CategoryType`.`id` = 2

This makes it difficult to order this query on CategoryType.name ASC. Any suggestions?

If joins are only option, do I have to unbind the models before querying? Will pagination work fine with joins?

Note! this is just a small part of all models, the resulting post->index need to fetch many other models through similar associations also.

(tested on cake 2.2.0 and v2.4.0-dev, php v5.4.11)

UPDATE! ---------

I just wanted to show my findings. I have now solved this without joins, but I had to re-bind in the model to get it working.

This is basically what I did to get it to work (also with paginations and sorts): In Post model: Added a bind function:

  $this->unbindModel(array(
    'belongsTo' => array('Category')
  ));

  $this->bindModel(array(
    'hasOne' => array(
      'Category' => array(
        'foreignKey' => false,
        'conditions' => array('Category.id = Post.category_id')
    ),
    'CategoryType' => array(
      'foreignKey' => false,
      'conditions' => array('CategoryType.id = Category.category_type_id')
    ))));

Then I added this to my index in Post controller:

  $this->Post->bindCategory();
  $this->paginate = array('contain' => array('Category' ,'CategoryType'));
  $this->set('posts', $this->paginate());

I include table headers also just for documentation:

<th><?php echo $this->Paginator->sort('CategoryType.name', 'Type'); ?></th>
<th><?php echo $this->Paginator->sort('Category.name', 'Category'); ?></th>

I hope this post can help others as well :)

I am also going to test this Behaviour to see if I can omit all the bind-functions as well: https://github.com/siran/linkable/

There are lots of plugins to cake, but cake should have a "certification" of the plugins. It is quite difficult to find the fully working and tested ones on github :)

I also miss a site like railscasts.com just for cake :D

/MartOn

I agree with you that CakePHP doesn't always generates efficient queries. I'm wondering if other ORM frameworks are better at this, however. In the end, it's good practice (as you did) to analyse the generated SQL statements and (if required) try to optimize them. I specifically mentioned if required, because sometimes it really depends on how often a query will be performed and optimization is critical. For very complex relations, it is possible to use custom (raw SQL) queries, but only as a last resort. — thaJeztah
A cleaner solution in those situations is to wrap your (complex) queries in a "database view" (CREATE VIEW post_with_category AS ... and use that as a database-source for your model. This way your CakePHP model can be kept simple and the database view will act as a 'abstraction layer'. dev.mysql.com/doc/refman/5.0/en/create-view.html IMO try to use CakePHP queries where possible, but don't force yourself into using them if things get so complicated that it will only make your code more difficult to understand and maintain. — thaJeztah

Dave Dave · Accepted Answer · 2013-03-13T17:48:45

Yes, you must use JOINs to be able to order based on an associated models results.

Yes, you can paginate with JOINs. Just pass your options (including JOINs) to your paginate prior to actually calling $this->paginate();. (there are many resources online for how to paginate with JOINs)

Multi level ordering in cakePHP

1 Answers