Search code examples
phpcakephpcontainable

CakePHP order query results on more than 1 level

I'm using the Containable behavior to get a list of Comments (belongsTo Post, which belongs to Question; Question hasMany Post, and Post hasMany Comments; all of these belong to Users).

$data = $this->Question->find ( 'first', 
    array ('contain' => 
        array ('User', 
               'Post' => array ('User', /* 'order' => 'User.created DESC'*/ )
        ) 
    ) 
);

It works, when I comment out the section in comments above. I suppose this is to be expected, but what I want is all of the Posts that are found, should be sorted in order of the 'created' field of the 'User' they belong to. How do I accomplish this deeper level sorting in CakePHP? I always get, "Warning (512): SQL Error: 1054: Unknown column 'User.created' in 'order clause'"

Thanks for your help!

Solution

  • Also, you might be trying to group on a related table from a find call that doesn't use joins.

    Set your debug level to something greater than 1 so you can see the query log and make sure that Cake isn't doing two queries to fetch your data. If that is the case then the first query is not actually referencing the second table.

    If you want to manually force a join in these situations you can use the Ad-Hoc joins method outlined by Nate at the following link.

    http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find