post habtm postlover : how to order posts by number of postlovers?

Question

Hi guys...please help me, i'm really struggling with this...

Authors can write post, and authors can love other authors' posts...

So posts belong to author (when authors write them), but posts habtm authors (when authors love them).

For example i'd like to get posts ordered by number of postlovers and created in the last 24 hours. Here's my models and join table:

TABLE: lovedposts_postlovers id: INT lovedpost_id: INT postlover_id: INT

POST MODEL

<?php
class Post extends AppModel {
var $name = 'Post';
var $belongsTo = 'Author';

var $hasAndBelongsToMany = array(
        'Postlover' =>
            array(
                 'className'              => 'Author',
                 'joinTable'              => 'lovedposts_postlovers',
                 'foreignKey'             => 'lovedpost_id',
                'associationForeignKey'  => 'postlover_id',
                'unique'                 => true
            )
    );

var $displayField = 'title';

}

?>

AUTHOR MODEL

<?php
class Author extends AppModel {
var $name = 'Author';
var $hasMany = 'Post';

var $hasAndBelongsToMany = array(
        'Lovedpost' =>
            array(
                 'className'              => 'Post',
                 'joinTable'              => 'lovedposts_postlovers',
                 'foreignKey'             => 'postlover_id',
                'associationForeignKey'  => 'lovedpost_id',
                'unique'                 => true
            )

    );

var $displayField = 'username';

}

?>

Answer 1

Your best option is to query on the joinModel. Usually that would be:

$this->Author->AuthorsLovedpost->find(...);

But since you're not sticking to the Cake naming conventions for the table that may or may not be different. The joinModel is automatically created BTW, but you can also explicitly specify it in the HABTM declaration.

var $hasAndBelongsToMany = array(
    'Postlover' => array(
         ...,
         'with' => 'joinModelName'
    )
);

For the find options you can do whatever you need, 'group' => 'post_id' and 'order' => 'COUNT(post_id)' or something to that extend. What you're looking for is getting the right set of 'post_ids' back.

Since from the point of view of the joinModel the Author and Post models are both belongsTo relationships, Cake will find related results accordingly and you can use the usual 'contain' options etc to filter results.

Hope that helps.

Answer 2

I think you should take a step back and try to read the documentation (book.cakephp.org). Try to make a demo using their examples.

• lovedposts_postlovers table is very confusing and maybe should be called something else, maybe authors_posts or even favorites. well, it can be anything as long as you specify it in 'joinTable'.

• lovedposts_postlovers should have the fields author_id, post_id

  //POST MODEL var $hasAndBelongsToMany = array( 'Author' => array( 'className' => 'Author', 'joinTable' => 'lovedposts_postlovers', 'foreignKey' => 'author_id', 'associationForeignKey' => 'post_id', 'unique' => true ) );

For example i'd like to get posts ordered by number of postlovers and created in the last 24 hours. Here's my models and join table:

$this->Post->LovedPosts->find('all', array('fields'=>array('Post.title', 'count(LovedPosts.*) as favorited'), 'group'=>'LovedPosts.post_id');

Basically you want to do a select count query and group by the post_id and this code should get you on the right track. Note: I didn't test this code. You also need an order clause in that find operation but I will leave that to you.