Ordering posts by their tag in a HABTM relationship

Question

I have correctly set up a HABTM relationship between Post and Tag. When the user navigates to a URL like http://site.com/tag/test I want to show all the posts tagged with test.

Using the following code gets the tag information and all the posts which have that tag:

public function view($name) {
    $this->set('tag', $this->Tag->findByName($name));
}

However, the posts it returns are not sorted by their created column, they seem to be retrieved on a "first come first serve" basis. I tried doing this:

public function view($name) {
    $this->set('tag', $this->Tag->findByName($name, array(
        'order' => array('Post.created DESC')
    )));
}

However that gave me an SQL error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Tag.' in 'field list'

SQL Query: SELECT DISTINCT Tag.` FROMportfolio.tagsASTagWHERETag.name` = 'test' LIMIT 1

Is there any way for me to order posts from newest to oldest in the query or do I have to reformat the result array in my controller?

Order only works from the model you are calling the find from, afaik this hasn't changed. You can't order on related models. You can try using the cakephp set class' sort function to order your results - book.cakephp.org/2.0/en/core-utility-libraries/…. Or try setting the order in your Post model or use the containable behavior. — Tim Joyce

tigrang tigrang · Accepted Answer · 2013-01-21T19:40:13

You need to do a find on the Post model and then force an ad-hoc join from the join table to the tag table, set a condition where Tag.name = $name and sort Post.created DESC

Or by rebinding some models. Example from cookbook:

$this->Recipe->bindModel(array(
    'hasOne' => array(
        'RecipesTag',
        'FilterTag' => array(
            'className' => 'Tag',
            'foreignKey' => false,
            'conditions' => array('FilterTag.id = RecipesTag.tag_id')
))));
$this->Recipe->find('all', array(
        'fields' => array('Recipe.*'),
        'conditions'=>array('FilterTag.name'=>'Dessert')
));

Apply it to your case (Recipe - > Post and RecipesTag -> PostsTag) and just add your sort

Ordering posts by their tag in a HABTM relationship

1 Answers