Distinct select where row in one of multiple tables

I’m building a feed for a project, and an object can appear in the feed depending from a couple of places.

I have a table of posts, which have a group and a visibility. Some require membership to view, others are public.

posts

id	group_id	visibility
1	1	public
2	1	member
3	2	public
4	2	member

Membership is recorded in a members table, with a start and end date for the membership:

members

id	user_id	group_id	period_start	period_end
1	1	1	2020-01-01	2020-02-01
2	2	2	2020-01-01	2020-01-01

At the moment, my query returns just the posts from the groups the user is a member of:

SELECT posts.*
FROM members
LEFT JOIN posts
ON
    posts.group_id = members.group_id AND
    posts.visibility IN ('member', 'public') AND
    posts.deleted_at IS NULL
WHERE
    members.user_id = XYZ AND
    members.period_start < now() AND
    members.period_end > now();

---

I want to add an the ability for a user to add a group to their favorites without becoming a member, which will show just the public posts in their feed.

favorites

id	user_id	group_id
1	1	2

How can I change the above query so it selects posts from the users membership groups, and also the public posts from their favorited groups?

It needs to be distinct, so if the user is both a member and has favourited the post doesn’t appear twice.

I’m expecting the result to look like the below for user_id 1:

id	group_id	visibility
1	1	public
2	1	member
3	2	public

Answer

I think you just want union all:

SELECT p.*
FROM members m JOIN
     posts p
     ON p.group_id = m.group_id AND
        p.visibility IN ('member', 'public') AND
        p.deleted_at IS NULL
WHERE m.user_id = XYZ AND
      m.period_start < now() AND
      m.period_end > now()
UNION ALL
SELECT p.*
FROM favorites f JOIN
     posts p
     ON p.group_id = f.group_id AND
        p.visibility IN ('public') AND
        p.deleted_at IS NULL
WHERE f.user_id = XYZ ;

Note the joins are inner joins, not outer joins.

I’m not sure if the favorites need the filter on active members or not; this does not include it.

You can also express this with two LEFT JOINs and some filtering:

SELECT p.*
FROM posts p LEFT JOIN
     favorites f
     ON f.group_id = p.group_id AND
        p.visibility IN ('public') LEFT JOIN
     members m LEFT JOIN
     ON p.group_id = m.group_id AND
        p.visibility IN ('member', 'public') AND
        m.period_start < now() AND
        m.period_end > now()
WHERE p.deleted_at IS NULL AND
      XYZ IN (m.user_id, f.user_id)