Q:

CakePhp order in find() by a count result and a date condition

i've got a HABTM relationship between Articles and Users with the join table ArticlesUser
in the articles_users table there are article_id,user_id and created.
Now i'm trying to order the results of my $this->Article->find('all',...) by the count of the article_id (in ArticlesUser join table) for each Article. In simple words: I want to sort the Articles by the amout of likes. Additionally it would be awesome if only the likes of the past two days would be considered (created coloumn).
Is this possible in cakephp without using query?
EDIT:
 
the HABTM relationship between articles and users is my "likes" relationship, so the join table is my likes table. 1 Article can be liked by n users and 1 user can like m articles. Now i'm trying to order my articles in a find() command with different (existing) conditions by the count of user_id (from the join table) of the last two days.
So the order of the articles depents on the count of (unique) user_id's for each article in the join table..
i hope this makes it a bit more clear! 

A:

We definitely need a lot more detail.  If you add more detail (models, where is the likes, ect) then I can even write the controller code for you.
To simply answer the sorting question you can use CakePHP built in functions.  In this case if you had:
array(
    [0] => array(
        ['Article'] => array(
            ['blah'] => 'blah',
            ['blah2'] => 'blah',
        ),
        ['ArticleUser'] => array(
            ['created'] => 'blh'
        )
    )
);

Then you could use:
Set::sort($results, '{n}.ArticleUser.created', 'DESC');

Using CakePHP's built in function will re-build your array, making the sort order whatever you desire.  You can see more of cakephp AWESOME built in array functions here:
http://book.cakephp.org/2.0/en/core-utility-libraries/set.html#Set::sort
Also, keep in mind that even though you have a HABTM relationship, you cannot use conditions of the join or relationship(especially for count).  Depending on your current setup, you may have to write the 'joins' manually in your query, or use containable behavior.
Best,
 Ryan
EDIT:
Try this.
$joins = array(
     array(
        'table' => 'articles_users',
        'alias' => 'ArticleUser',
        'type' => 'inner',
        'conditions' => array(
            'Article.id = ArticleUser.article_id',
            )
        )
    );

$this->Artical->find('all', array(
    'conditions' => array(
        'ArticleUser.created >' => $this->Time->fromString('-2 days')
        ),
    'joins' => $joins,
    'fields' => array('Article.*', 'COUNT(Article.id) as Article.likes'),
    'group' => 'Article.id',
    'order' => 'ArticleUser.created DESC'
    )
);

If everything works you should get your 'likes' for the last 2 days per article in
Article['likes'];

Keep in mind you can ignore everything before this edit because your results will already be sorted by the query.
Best, 
 Ryan

