How to filter records by join table

[Triquetra]

Given the following Models:

class User(Model):
    name = Field()
    has_many('memberships', {'organizations': {'via': 'memberships'}})


class Organization(Model):
    name = Field()
    has_many('memberships', {'users': {'via': 'memberships'}})


class Membership(Model):
    belongs_to('user', 'organization')
    role = Field()

How would one get a list of User records with membership at Organization X and role == 'admin'?

The documentations talks about how to add a record with a role on the join table, but I can't figure out how to access the join table data from the User records.

[gi0baro]

@Triquetra you can reach the same result in different ways.

In case you have the organization record:

org = Organization.get(name="X")
admin_users = org.users.switch(Membership).where(lambda m: m.role == "admin").select()

or without switch:

admin_users = org.users.where(Membership.role == "admin").select()

with a single query using switch:

admin_users = User.all().join('organizations').switch(Organization).where(
    lambda o: o.name == "X"
).switch(Membership).where(
    lambda m: m.role == "admin"
).select()

with a single explicit query:

User.all().join('organizations').where(
    (Organization.name == "X") & (Membership.role == "admin")
).select()

[Triquetra]

I cannot find any documentation on the switch method. Could you point me to the appropriate page so that I can learn more about it?

[gi0baro]

@Triquetra It seems I forgot to document it. Will open up an issue to update documentation.

[Triquetra]

This was very instructive. Thank you, @gi0baro!