get the current user like in the post query

Question

I have a table that stores posts. I want to create a query to retrieve posts and if the current user liked this post. This is my query:

select *,CAST(iif(exists(select 1 from Post.PostLike where PostId=ps.Id and UserId=@UserId),1,0) as bit) as IsLiked
from Post.Post ps

Is there a query better than this?

Do you mean performance tuning like mentioned [here](https://stackoverflow.com/questions/4444085/are-there-any-query-optimization-tools-for-sql-server)? — vahdet, Jun 17 '18 at 11:07
You'd probably get a performance increase from using `APPLY`, rather than a subquery in your `SELECT`. The former should be evaluated once for the dataset, where as the latter once for every row. — Thom A, Jun 17 '18 at 11:10

sticky bit · Accepted Answer · 2018-06-17T20:51:45.750

Try using a LEFT JOIN.

SELECT ps.*,
       convert(bit, CASE
                      WHEN psl.userid IS NULL
                        THEN 0
                      ELSE 1
                    END) isliked
       FROM post.post
            LEFT JOIN post.postlike psl
                      ON ps1.postid = ps.id
                         AND psl.userid = @userid;

And then create an index on post.post.id (unless it already has one, which should be the case for a primary key column) and an index on post.postlike.postid and post.postlike.userid (again unless such an index already exists, which might be the case it the columns are the primary key, which is reasonable).

CREATE INDEX post_id
             ON post.post
                (id);

CREATE INDEX postlike_postid_userid
             ON post.postlike
                (postid,
                 userid);

Or alternatively change the last one to

CREATE INDEX postlike_userid_postid
             ON post.postlike
                (userid,
                 postid);

if there are more users than posts.

Check the execution plan if the indexes help.

get the current user like in the post query

1 Answers1