Hi everyone I'am new to laravel I want to get result of all posts and also want flag that logged user has liked that post or not as like instagram posts (if logged user has liked the post then flag postIsLiked as 1 so i can display like heart icon as red else black heart(not liked) )

MY MAIN TABLES AND THEIR RESP FEILDS ARE AS FOLLOWS :

1. posts = id(PK), user_id(FK),post_caption,...
2. users= id(PK), username,fname,lname,....
3. post_likes = id(PK),post_id(FK),user_id(FK),

And this is the query that I have tried , I dont know how can I achieve this

$postData = DB::table('posts')
    ->leftJoin('users', 'users.id', '=', 'posts.user_id')
    ->leftJoin('user_profile', 'user_profile.id', '=', 'posts.user_id')
    // ->leftJoin('post_likes' , 'posts.id' '=','post_likes.post_id')
    ->select( 'posts.id as postId' ,'users.*','posts.post_caption','posts.post_media','posts.created_at','user_profile.profile_pic','post_likes.id')
    ->get()->toArray();

Please help me in this , Thanks in advance

CodePudding user response：

We need to take the leftJoin of the posts on the likes of the current user.
I've tested this query and it should work:

$postData = DB::table('posts')
        ->leftJoin('users', 'posts.user_id', '=', 'users.id')
        ->leftJoin('user_profiles', 'posts.user_id', '=', 'user_profiles.id')
        ->leftJoin('post_likes', function ($join) {
            $join->on('posts.id', '=', 'post_likes.post_id')
                ->where('post_likes.user_id', '=', Auth::id());
        })
        ->select('adjust the columns accordingly', 'post_likes.id as likeId')
        ->get()->toArray();

If likeId == NULL then the current user hasn't liked the post, else it will have a value. Using this id will make it simpler to dislike the Post by deleting the like.

Just an observation: we are joining the Profiles on the id of the User, it is not obvious that the ids will be the same.

CodePudding user response：

Kindly replace it with this query. I added IF condition to check for the postIsLiked flag.

$postData = DB::table('posts')
    ->leftJoin('post_likes' , 'posts.id' '=','post_likes.post_id')
    ->leftJoin('users', 'users.id', '=', 'post_likes.user_id')
    ->leftJoin('user_profile', 'user_profile.id', '=', 'users.id')
    ->select('posts.id as postId', 'users.*', 'posts.post_caption', 'posts.post_media', 'posts.created_at', 'user_profile.profile_pic', 'post_likes.id', DB::raw('IF (post_likes.user_id = '.auth()->user()->id.', "1", "0") as postIsLiked'))
    ->get()->toArray();

Let me know the results.