Joining multiple separate CTE's
I am working on a complex query. I can get it to run efficiently using multiple CTE's(Atleast in RAW SQL) but I want to port all this over to Kysely due to the typesafety.
What I want is something like this:
const cteOne = kyselyDb
.with('myCTE', (qb) =>
qb
.selectFrom('InboxFeedItem')
.limit(10)
.selectAll()
)
.as('cteOne'),
const cteTwo = kyselyDb
.with('otherCTE', (qb) =>
qb.selectFrom('InboxFeedItem').offset(10).limit(10).selectAll(),
)
.selectFrom('otherCTE')
.select('id as xId')
.as('cteTwo')
I could have a number of different CTE's (possibly returned from function calls) And I want to then JOIN them OR UNION them etc. but right now its not letting me. Maybe I don't know the syntax or is it not possible ? TYIA
Solution:Jump to solution
conditionally added CTEs are not supported - too much type complexity involved.
you could
union with a subquery conditionally tho, as the result shape is already established, and union can be added to existing builder instances, outside of chain (but also with $call)....8 Replies
Hey 👋
Can you share the SQL you're going after?
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;
I know I can chain
.with in Kysely as well but I can't do that as sometimes I would conditionally need to create a CTE as wellSolution
conditionally added CTEs are not supported - too much type complexity involved.
you could
union with a subquery conditionally tho, as the result shape is already established, and union can be added to existing builder instances, outside of chain (but also with $call).thanks!
I assume the type complexity problem would also be there then for conditional joins as well ?
Yeah
Highly recommended to avoid these patterns if possible.
ohh okay, got it
the recommendation then as I see, is to use
union or the $call operator in such a scenario.$call((db) => db.union(subquery)) OR query = query.union(subquery) is a matter of taste.Thank you so much!