CakePHP containable order condition not working

Question

I'm having problems with a very simple ordering query. I have a Post model and a Tag model with a HABTM relationship and am trying to return a list of all posts with a particular tag assigned to them, ordered by the date the post is created.

$this->set('data', $this->Post->Tag->find('all', array(
    'conditions' => array('Tag.id' => 1),
    'contain' => array('Post' => array(
        'order' => 'Post.created_date desc'
    ))
)));

While this returns the list of posts, it is not sorted by date.

With debugging on, it looks like the following query is being used:

SELECT `Post`.`id`, `Post`.`title`, `Post`.`created_date`, `PostsTag`.`post_id`, `PostsTag`.`tag_id`
FROM `database`.`posts` AS `Post`
JOIN `database`.`posts_tags` AS `PostsTag` ON (`PotsTag`.`tag_id` = 1 AND `PostsTag`.`post_id` = `Post`.`id`)

Code for posts model:

class Post extends AppModel {
    public $name = 'Post';
    public $hasAndBelongsToMany = array('Tag');
}

Code for tags model:

class Tag extends AppModel {
    public $name = 'Tag';
    public $hasAndBelongsToMany = array('Post');
}

Any help on the issue would be much appreciated - I'm using CakePHP 2.1. if it makes any difference.

See below this url:- http://book.cakephp.org/1.3/view/1044/hasAndBelongsToMany-HABTM — Abid Hussain, Jul 25 '12 at 11:06
Hi Abid, I've reviewed the HABTM page on the CakePHP site but don't see any reference to ordering of a results using conditions like this - please could you point out the relevant area relating to my query. — Loftx, Jul 28 '12 at 17:34
Just to be sure: did you state That the Tag model $actsAs containable? — Bart Gloudemans, Jul 29 '12 at 21:35

score 3 · Answer 1 · edited Sep 06 '13 at 18:47

3

What about defining the order attribute in your Tag Model?
Like e.g.

var $hasAndBelongsToMany = array(
    'Post' => array(
        'order' => 'Post.created_date'
    )
);

edited Sep 06 '13 at 18:47

Blazemonger

90,923
26
142
180

answered Jul 28 '12 at 22:19

Bart Gloudemans

1,201
12
27

Thanks for your response - while this solves my original query, I would like to be able to set the order dynamically e.g. so the posts can be ordered by title or another column as well as just by created date. – Loftx Jul 29 '12 at 13:36
@Loftx then just bind models dynamically – gaRex Nov 29 '13 at 05:31

score 1 · Answer 2 · answered Jul 24 '12 at 21:09

1

I don't think that the "order" should be inside of "contain". Try with:

$this->set('data', $this->Post->Tag->find('all', array(
    'conditions' => array('Tag.id' => 1),
    'contain' => array('Post'),
    'order' => 'Post.created_date desc'
)));

or just:

 $this->set('data', $this->Post->Tag->find('all', array(
        'conditions' => array('Tag.id' => 1),
        'order' => 'Post.created_date desc'
 )));

answered Jul 24 '12 at 21:09

Thanks for the quick response - I have tried both your suggestions and both produce the error: Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Message.created_date' in 'order clause': SQL Query: SELECT Tag.id, Tag.name FROM database.tags AS Tag WHERE Tag.id = 1 ORDER BY Post.created_date desc – Loftx Jul 24 '12 at 21:29
No problem. :) Well, I have some ideas but I must see a DB and the code. You can add me on MSN, Facebook or Skype (or just send me an email) and give me more information about DB and the code. I'll try to solve this problem and I'll put the solution here so anyone can see it. – Jul 24 '12 at 21:33
Hi Davor, I've emailed you the code - if you're able to help please post your answer on Stack Overflow and I'll accept it. – Loftx Jul 28 '12 at 17:30
I will but I'm busy right now. I'll respond you in a few hours. – Jul 28 '12 at 21:23

score 0 · Answer 3 · answered Jul 28 '12 at 21:59

Read this:-

http://www.jamesfairhurst.co.uk/posts/view/adding_tags_to_a_cakephp_app_hasAndBelongsToMany/

http://edivad.wordpress.com/2007/04/19/cakephp-hasandbelongstomany-habtm/

//try this

CREATE TABLE `tags` (
  `id` int(11) NOT NULL auto_increment,
  `tag` varchar(100) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY  (`id`)
);


CREATE TABLE `posts_tags` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
);


//Creating the Models and Relationships

class Tag extends AppModel {
    var $name = 'Tag';
    var $hasAndBelongsToMany = array('Post'=>array('className'=>'Post'));
}

class Post extends AppModel {
    var $name = 'Post';
    var $hasMany = array('Comment'=>array('className'=>'Comment'));
    var $hasAndBelongsToMany = array('Tag'=>array('className'=>'Tag'));
}

CakePHP containable order condition not working

3 Answers3