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.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community