Question 183609 in Laravel Question posted in
The official Laravel Documentation can be found here.

I need Laravel Query for Post list with details of user who created post and a flag which says if any post is liked by requesting user

I am learning Laravel. I need your help for the following case.

I have created 3 tables names users, posts, post_likes. Each Table contains fields as mentioned below.

1. users table

— id, username,profile_pic,email (id is the Primary key)

2. posts table

— id, title, photo, user_id (Where id is primary key and user_id is pk of users table)

3. post_likes table

— id, post_id, user_id (where id is the primary key and post_id is pk of posts table and user_id is pk of users table)

Now, I need a list of all posts with all data in posts table, user details who created particular post and a flag for each post which says if the post is liked by the logged in user or not.

Please help me to write a query with using Laravel.

I have written following query:

$posts = DB::table('posts')
                 ->leftJoin('post_likes', 'posts.id', '=', 'post_likes.post_id')
                 ->leftJoin('users', 'posts.user_id', '=', 'users.id')
                 ->select(DB::raw('posts.*,users.name,users.profile_pic, (CASE WHEN post_likes.user_id = ''.$request->user_id.'' THEN "1" ELSE "0" END) as liked'))
                 ->orderBy('posts.id', 'desc')
                 ->get();

Which gives me following result:

{
    "status": true,
    "posts": [
        {
            "id": "8",
            "user_id": "59",
            "title": "This is test post from Rocky",
            "photo": "JyWhsvA57BDlCYLUpJksbmgFsoB1P.jpg",
            "name": "Green",
            "profile_pic": "jHAqnRWyVi1wl83m207Pc415Tgi.jpg",
            "liked": "0"
        },
        {
            "id": "9",
            "user_id": "9",
            "title": "This is test post from Ruby",
            "photo": "",
            "name": "Sandy",
            "profile_pic": "nRWyVi1wl83m207Pc415Tgi.jpg",
            "liked": "0"
        },
        {
            "id": "3",
            "user_id": "10",
            "title": "Let's hangout",
            "photo": "J54fctcGMsh6f5OC3DvWqISurO.jpg",
            "name": "Jhonty",
            "profile_pic": "E1N5qEVJPQ67YSyHo8MljQNvF.png",
            "liked": "0"
        },
        {
            "id": "3",
            "user_id": "10",
            "title": "Let's hangout",
            "photo": "J54fctcGMsh6f5OC3DvWqISurO.jpg",
            "name": "Jhonty",
            "profile_pic": "E1N5qEVJPQ67YSyHo8MljQNvF.png",
            "liked": "1"
        }
    ]
}

As you can see I am receiving the post with id = 3 two times. I need it only once and in which like should be 1. Currently I am getting two entries in which one has likes= 0 and the other have like = 1 for the same post.

this happens when same post is liked by multiple users. Means post_likes table has entries as following

id     post_id    user_id

1      1           5
2      3           10
3      3           9

So based on this values I am getting the post with id 3 two times in result.

Please feel free to ask for any questions.

Thanks in advance

Tags:

2

Answers


  1. 0 
     $loggedInUserId = $request->user_id;

 $posts = DB::table('posts')
   ->leftJoin('users', 'posts.user_id', '=', 'users.id')
   ->leftJoin('post_likes', function ($join) use ($loggedInUserId) {
       $join->on('posts.id', '=', 'post_likes.post_id')
         ->where('post_likes.user_id', '=', $loggedInUserId);
   })
   ->select('posts.*', 'users.name', 'users.profile_pic')
   ->addSelect(DB::raw('(CASE WHEN post_likes.id IS NOT NULL THEN 1 ELSE 0 END) as liked'))
   ->orderBy('posts.id', 'desc')
   ->get();

    In this updated query, a subquery is used within the leftJoin clause for the post_likes table. It filters the likes based on the logged-in user’s ID and checks if a like exists for each post.

    Hope this helps. Happy coding.

    Login or Signup to reply.
  2. 0 
    // get posts
$posts = DB::table('posts')
                 ->leftJoin('users', 'posts.user_id', '=', 'users.id')
                 ->select(DB::raw('posts.*,users.name,users.profile_pic'))
                 ->orderBy('posts.id', 'desc')
                 ->get();

// make posts assosiative arrays
$posts = json_decode(json_encode($posts), true);

// for each post find if it's liked by the user
foreach ($posts as &$post) {
  $liked = DB::table('post_likes')
    ->select(DB::row('COUNT(*) AS liked'))
    ->where('post_id', '=', $post['id'])
    ->where('user_id', '=', $request->user_id)
    ->get()
    ->liked;

  $post['liked'] = $liked;
}
unset($post);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search