Complex query question

[OscarGodson]

I need a query that finds all the families that user1 and user2 are a part of (think of families as "groups") and where user1 is an admin and user2 is not. In SQL I'm able to do this with a self join and a ON TRUE. I was curious if this was possible with Geddy Model. I have the following:

    MembershipModel.all({or:[
      { userId: params.payee_id, permissionLevel: 0 },
      { userId: params.payer_id, permissionLevel: 1}
    ]}, { includes: ['families'] }, function (err, membershipModels) {
      console.log(membershipModels)
    });

This gets all memberships for payee_id where their permission level is 0 OR where payer_id has a permission level of 1, but it doesn't find where they are in the same family. Is there anyway to do this? Here's the SQL:

SELECT fm1.family_id
FROM family_memberships fm1
JOIN family_memberships fm2 ON TRUE
 AND fm1.family_id = fm2.family_id
 AND fm1.user_id != fm2.user_id
WHERE TRUE
AND fm1.permission_level = 1
AND fm2.permission_level = 0
AND fm1.user_id = "094EF50E-0DE1-4F86-B0A7-BEE6ED02638A"
AND fm2.user_id = "204107AF-EF72-4903-9C73-F06EAC35BA04"

[danfinlay]

I'm not 100%, but I suspect this is going to be a matter of querying on eager loaded associations.

I'm imagining a many-to-many situation, where a Family has many MembershipModels, and so do Users.

A rough prototype of what I suspect would work is:

Family.all({
  and:[
    'membership.userId': firstUserId,
    'membership.userId': secondUserId,
  ]
}, { includes: 'Membership'}, function (err, families) {
  console.log("Families that have both members:");
  console.dir(families);
});