Need to convert OUTER JOIN with multiple "AND"

Question

I have this raw sql I'd like to convert to Esqueleto. It should bring all the groups that user ID 1, isn't registered to via the group_membership table.

SELECT * 
FROM group
LEFT OUTER JOIN group_membership
  ON (group.id = group_membership.group_id AND group_membership.user_id=1)
  WHERE group_membership.group_id IS NULL

I'm able to see some related info on how to do the `IS NULL`, but still stuck on the multiple `AND` - https://github.com/prowdsponsor/esqueleto/issues/50#issuecomment-37094450 — amitaibu, Aug 13 '16 at 19:57
And the `AND` inside the `on` answered here -- https://github.com/prowdsponsor/esqueleto/issues/126#issuecomment-172010758 — amitaibu, Aug 13 '16 at 20:24

score 0 · Answer 1 · answered Aug 13 '16 at 20:26

And here is the answer I gathered from different issues:

getGroups userId = do
    runDB
        . E.select
        . E.from $ \(group `E.LeftOuterJoin` groupMembership) -> do
            E.on $ E.just (group ^. GroupId) E.==. (groupMembership ?. GroupMembershipGroupId) &&.
                   (groupMembership ?. GroupMembershipUserId) E.==. E.just (E.val userId)
            E.where_ $ E.isNothing (groupMembership ?. GroupMembershipGroupId)
            return
              ( group ^. GroupId
              , group ^. GroupTitle
              )

Need to convert OUTER JOIN with multiple "AND"

1 Answers1