CODEWITHSUNDEEP
cakephpcakephp-3.0
Mirko Pagliai
Mirko Pagliai

Reputation: 1241

CakePHP 3.x: query for related posts

I have Posts and Tags models, related with a "belongs to many" association.

So in my database I have posts and tags tables and a posts_tags table with post_id and tag_id fields. The tags table has the post_count field, showing the number of posts that belong to that tag.

When I get a post I get also tags related to it.

$this->Posts->find()
    ->contain(['Tags'])
    ->where(['Posts.id' => $id])
    ->first());

Now, for each tag, I would like to get a post that has that tag, excluding the initial post, sorted creation date (created field). The important thing is that for each tag is getted a post other than those already getted.

I could use a foreach and for each tag get a post that contains it, by excluding the id of the initial post and those already getted.

I want to know if I can do this with a single query and have an example to work from.

Thanks.

EDIT
Temporary solution, which uses a query for each tag

First, I get the main post:

$post = $this->Posts->find()
    ->contain(['Tags'])
    ->where(['Posts.id' => $id])
    ->first();

In this case the post is recovered through its ID, but you can do differently. Also you can use the cache.
The important thing is the $post variable.

Now (here is a good idea to use the cache...):

//Tries to gets related posts from cache
$related = Cache::read($cache = sprintf('related_posts_for_%s', $post->id), 'posts');

if(empty($related)) {
    $tags = $post->tags;

    //Re-orders tags, using the "post_count" field, then based on the popularity of tags
    usort($tags, function($a, $b) { return $b['post_count'] - $a['post_count']; });

    //Gets only the first 5 tags
    $tags = array_slice($tags, 0 , 5);

    //This array will be contain the ID to be excluded
    $exclude = [$post->id];

    //Gets a related post for each tag
    //Reveres the tags order, because the tags less popular have less chance to find a related post
    foreach(array_reverse($tags) as $tag) {
        $post = $this->Posts->find('active')
            ->select(['id', 'title', 'slug'])
            ->matching('Tags', function($q) use($tag) {
                return $q->where(['Tags.id' => $tag->id]);
            })
            ->where(['Posts.id NOT IN' => $exclude])
            ->first();

        //Adds the post to the related posts and its ID to the IDs to be excluded for the next query
        if(!empty($post)) {
            $related[] = $post;
            $exclude[] = $post->id;
        }
    }

    Cache::write($cache, $related, 'posts');
}

//Related posts
debug($related);

NOTE: after this code, the $post variable no longer contains the original post. Pay attention or use a different variable name for related posts.

Upvotes: 1

Views: 185

Answers (1)

arilia
arilia

Reputation: 9398

try this (not tested)

$this->Posts->find()
->contain([
    'Tags',
    'Tags.Posts' => function($q) use($id) {
        $q->where(['id !=' => $id])
            ->order(['created' => 'desc'])
            ->limit(1);
        return $q;
     }
   ])
->where(['Posts.id' => $id])
->first());

But in this way you'll may get a Post two times (and I see now that you don't want this)

Upvotes: 1

Related Questions