Substitute for RIGHT JOIN

SQL++
1

DISCLAIMER: Everything here assumes we have a normalized model. Please assume this as a condition for your analysis. Please do not suggest to de-normilize, since this is not part of the question.

I have the following query:

SELECT META(t).id AS taskKey, t.title, t.description, t.type
  assig AS assignee,
  u AS createdBy,
  ARRAY_AGG(DISTINCT c) AS comments,
  ARRAY_AGG(DISTINCT attch) AS attachments
FROM testBucket t
  LEFT JOIN testBucket c ON KEY c.parentKey FOR t
  LEFT JOIN testBucket attch ON KEY attch.parentKey FOR t
  LEFT JOIN testBucket assig ON KEYS t.assigneeUserKey
  LEFT JOIN testBucket u ON KEYS t.createdBy
WHERE t.documentType = 'Task'
  AND c.documentType = 'Comment'
  AND attch.documentType = 'Attachment'
  AND assig.documentType = 'User'
  AND u.documentType = 'User'
  AND t.assigneeUserKey = 'XXXXXX'
GROUP BY META(t).id

The model corresponds to normalized documents for tasks. Each task keeps tracking about who created it and could be assigned to a user. And finally, each task has 0 or more comments and 0 or more attachments. Each document has a documentType field.

The query does work for tasks containing both comments and attachments. But it doesn’t if one of them is missing. It’s somehow clear that the left side on a “ON KEY” join is the child entity (comments and attachments in this case), so it would be expected for this query to bring only matches with records on the left side of the join. The unsupported RIGHT JOIN would do the trick in this case. Is there any option that could work for this model?

2

You can do the following:

AND (c IS MISSING OR c.documentType = 'Comment')
AND (attch IS MISSING OR attch.documentType = 'Attachment')
1 Like
3

No, it still only brings records with children on both attachments and comments. And I think this makes sense; this would work if they were on the right hand side of the join.

I am thinking one solution with something like (omitting bucket names and documentType checks):

SELECT /* needed fields */
  FROM (SELECT  /* needed fields */
      FROM tasks
        LEFT JOIN attachments ON KEY attachments.parentKey FOR t) AS tasksWithAttachments
    LEFT JOIN comments ON KEY comments.parentKey FOR /* tasksWithAttachments => I die here, hehe */
4

Hmmm, surprising. Can you try:

AND (c IS NOT VALUED OR c.documentType = 'Comment')
AND (attch IS NOT VALUED OR attch.documentType = 'Attachment')

I’m pretty sure this is solvable.

1 Like
5

It worked. An I was wrong with “IS MISSING”; it also worked. I will test different scenarios further, but this seems to have solved the problem. Thank you so much!

1 Like