I think this SQL Fiddle is a way of achieving what I want: http://www.sqlfiddle.com/#!2/e5bab/5

If your SQL query is correct (I don't know why there is "join users u" though), it can be written using Query Builer like so:

DB::table('posts')
    ->leftJoin('user_likes', function($join) {
        $join->on('user_likes.post_id', '=', 'posts.id')
            ->where('user_likes.user_id', '=', 1);
    })
    // If you want you may select only some of the fields like so:
    //->select('posts.id as post_id', 'posts.title', 'user_likes as did_i_like')
    ->groupBy('posts.id')
    ->orderBy('posts.id');

Hello, thanks for your answer Amegatron!

I have one question: Right now, I have the paginated posts cached, and then each User has a cached collection of the posts ids that they liked, so when they log in, I'll check if each post is in the user's liked post collection.

$liked = Auth::user()->getLikedPosts(); // returns a cached collection of the User liked posts ids
$posts = Post::popular(1); // returns a cached and paginated index of the most popular posts.

Then, I'll check if the liked collection contains the post id:

foreach($posts as $post)
{
  .......
  if($liked->contains($post->id)
   {
     // show red Heart
   }
   else
   {
    // show gray heart
   }
  .......

I think It's not a good way of doing it, since the $liked collection will be growing a lot... However, with the new Query, I really don't know how could I cache the posts, so that I don't have to query the Database each time a user logs in or navigates throught the posts.

Thanks in advance! :)

I'm also currently thinking of implementing "likes" functionality on my site. And if I were you, I would certainly cache likes counter inside Post model. So, I think there is no problem getting the list of popular posts using query builder like I described earlier: you would simply get the list of posts with already-set flag telling whether current user has liked a certain post or not. So, for the popular posts the last orderBy would change to smth like orderBy('posts.likes', 'DESC');

Hello again!

So, if I understand, the posts would never be cached? I mean, If I have many logged in users, navigating throught the website, every time they refresh or change section a new query would be executed to the database, so that I'd end up with many queries at the same time, right? I think that it could be a problem

Thanks in advance :)

You may use your suggested approach, but instead of retreiving all liked posts, you may add whereIn clause to select only those which are in $posts collection. And then again walk through the $posts collection to determine, whether it is in $likes collection. I think that will be fast enough.

Gelurban said:

Hello, thanks for your answer Amegatron!

I have one question: Right now, I have the paginated posts cached, and then each User has a cached collection of the posts ids that they liked, so when they log in, I'll check if each post is in the user's liked post collection.

$liked = Auth::user()->getLikedPosts(); // returns a cached collection of the User liked posts ids $posts = Post::popular(1); // returns a cached and paginated index of the most popular posts.

Then, I'll check if the liked collection contains the post id:

foreach($posts as $post) { ....... if($liked->contains($post->id) { // show red Heart } else { // show gray heart } .......

I think It's not a good way of doing it, since the $liked collection will be growing a lot... However, with the new Query, I really don't know how could I cache the posts, so that I don't have to query the Database each time a user logs in or navigates throught the posts.

Thanks in advance! :)

@Gelurban what is your code in getLikedPosts() function? I want to know how to cache the relation.