Using sql to determine if the user has liked a post

Question

Use-case. We have users posts and likes tables. I am a user with id 4 who wants to get a list of posts and for each individual post I'd like to know if I liked it already or not.

I know this is sounds like a trivial problem, but I can't figure out a simple solution that does not involve using the union and the subquery..

Consider following data structure:

CREATE TABLE users(
    id bigint NOT NULL PRIMARY KEY
);

CREATE TABLE posts(
    id bigint NOT null PRIMARY KEY,
    userId bigint NOT NULL references users(id),
    name varchar(40) not null
);

CREATE TABLE likes(
    userId bigint NOT NULL references users(id),
    postId bigint NOT NULL references posts(id),
    PRIMARY KEY(userId, postId)
);

insert into users values (1),(2),(3),(4);

insert into posts values (1, 1, 'Post 1, user 1'), (2, 1, 'Post 2, user 1');
insert into posts values (3, 2, 'Post 3, user 2'), (4, 2, 'Post 4, user 2');
insert into posts values (5, 4, 'Post 5, user 4'), (6, 4, 'Post 6, user 4');

insert into likes values (1, 1), (1, 2);
insert into likes values (2, 1);
insert into likes values (3, 5);
insert into likes values (4, 1), (4, 5);

Then, if I want to get the posts that I liked I use (I am user id=4 in this example)

-- get what I like
select 
    p.*
    , true as isLiked
from posts p
left join likes l on l.postId = p.id
where 1=1
    and l.userid = 4
order by p.id;

Then, if I want to select the rest of the posts I use:

-- get the rest of the posts
select 
    p.*
    ,false as isLiked
from posts p
where 1=1
    and not exists (select 1 from likes l where l.postid = p.id and l.userid = 4)
order by p.id;

Finally, the query that accumulates both of these solutions:

-- result
select * from (
select 
    p.*
    , true as isLiked
from posts p
left join likes l on l.postId = p.id
where 1=1
    and l.userid = 4
union ALL
select 
    p.*
    ,false as isLiked
from posts p
where 1=1
    and not exists (select 1 from likes l where l.postid = p.id and l.userid = 4)
) p
order by p.id;

SQL fiddle: http://sqlfiddle.com/#!17/50108b/1

Back to my question. Is this my imagination or have I overly complicated the query? instead of a single query with a JOIN I ended up with a couple of queries joined in union and a subquery.. Is there a more friendly approach for the task?

I am using postgresql of latest version, but of course since it's an sql then I guess answers in other languages are just as must welcomed..

Note: after I have written the question - stackoverflow suggested a topic with a similar question so I was able to solve the problem that way.. The answer is provided below, however, feel free to comment if you have something to say on the subject..

Answer 1

Later, following the solution in thread here: Many-to-many relationship to determine if user has liked a post

I was able to come up (copy?) with alternative solution that yields the same result:

select
    p.*
    ,EXISTS(SELECT * FROM likes l WHERE l.postid = p.id and l.userid = 4) AS isLiked
FROM posts p
order by p.id;

While substantially reducing the complexity.

I ran the explain command and it does look to perform better on paper.. Not sure if the best solution of all but works fine in my scenario while only a few records in table are present..