Raizel
Joining multiple separate CTE's
This is a part of it:
WITH Y AS (
SELECT i.id as ifiId, c.id as convId FROM public."InboxFeedItem"i inner join "Conversation" c on i."conversationId" = c.id
inner join "ConversationOwner"co on c."conversationOwnerId" = co.id
where co."instagramAccountId"= '3b133c07-4210-4cca-b85a-a9ef08b632cd'
order by i."previewTimestamp" desc limit 10
),
Z AS (
SELECT * FROM
Y inner join "Message" mmsg on Y.convId = mmsg."conversationId"
inner join "Content" content on mmsg."originContentId" = content.id
where content."platformContentId"='17969804336852591'
),
REPLIED_TO_POST_OR_STORY AS (
SELECT i.id as xId FROM "InboxFeedItem" i inner join "Conversation" c on i."conversationId" = c.id
inner join "CommunityMember" participant on c."participantId" = participant.id
left join "CommentOwner" co on participant.id = co."communityMemberId"
left join "Comment" on co.id = "Comment"."authorId"
left join "Content" content on "Comment"."contentId" = content.id
where content."platformContentId"='17969804336852591'
)
SELECT ifiId FROM Z
UNION
SELECT xId from REPLIED_TO_POST_OR_STORY;
14 replies