CODEWITHSUNDEEP
pythonpeewee
SmittySmee
SmittySmee

Reputation: 400

Peewee ORM - Filtering Result with Many-To-Many Relation

All,

I am trying to query a system of users and address using Peewee. I am attempting to filter out the many-to-many relationships. I get back the correct objects, but I am not getting the non-matching relationships removed when I return the objects via list(query). For example, if I want to get all the addresses of a user that have the state of 4 in New York (NY), I would perform a query as such:

Setup:

class Address(Model):
    address_line_1 = CharField(null=True)
    address_line_2 = CharField(null=True)
    city = CharField(null=True)
    state_abbr = CharField(null=True, max_length=2)
    state = IntegerField(default=NEW_ID, choices=STATE_CHOICES)

class LicenseNumber(Model):
    number = CharField(unique=True, index=True)
    state = IntegerField(default=NEW_ID, choices=STATE_CHOICES)

class User(Model):
    license = ForeignKeyField(LicenseNumber, null=True)
    addresses = ManyToManyField(Address, related_name="users")
    state = IntegerField(default=NEW_ID, choices=STATE_CHOICES)

Example:

def get_filtered_users(...):
    # Get the through model for the many-to-many relationship
    UserAddresses = User.addresses.get_through_model()

    # Query users & license & addresses
    query = (User
             .select(User, Address, LicenseNumber)
             .join(UserAddresses)
             .join(Address)
             .switch(User) # Switch the context back to the user
             .join(LicenseNumber)
             .where(  # Filter out addresses that are not in NY & not state 4
                   (User.state == 4) & 
                   (Address.state_abbr << ['NY']) &
                   (Address.state == 4))
             .having(  # Also tried to ensure that I only get what we want 
                   (Address.state == 4) & 
                   (Address.state_abbr << ['NY'])))

    # Return the users
    return list(query)

When I view the objects view query.dict(), I can look at the items returned, and they are correct. Is there a way to return the instantiated User objects with the associated relationships that are filtered? When viewing the filtered records, I am expecting to see the relationships filtered out when I perform the query.

Expected Result:

User 1:

Address 1

Actual Result:

User 1:

Address 1

Address 2:

The goal would be to use the User objects and reference the addresses that are filtered via:

# Return the users & their filtered information
return list(query)

Am I missing something to filter out the records within the relationship when iterating the record instances? Any help would be great

Upvotes: 1

Views: 1225

Answers (1)

SmittySmee
SmittySmee

Reputation: 400

This took me a while to figure out by searching and reading through the docs pretty closely.

The solution is to use the aggregate_rows function PeeWee supplies. This function helps against N+1 queries (where you need to fetch the related records).

Essentially, this will select the relationships defined within the SELECT statement. Because we are doing a many-to-many relationship when looking for Addresses' state, we will need the three tables involved. In this case, it would be Users, UserAddresses (the through table), and Addresses all within the select statement. At the end the query we need to call the aggregate_rows() to perform the fetches and combinations.

The code should look like this:

def get_filtered_users(...):
    # Get the through model for the many-to-many relationship
    UserAddresses = User.addresses.get_through_model()

    # Query users & license & addresses
    query = (User
         .select(User, Address, UserAddresses, LicenseNumber)
         .join(UserAddresses)
         .join(Address)
         .switch(User) # Switch the context back to the user
         .join(LicenseNumber)
         .where(  # Filter out addresses that are not in NY & not state 4
               (User.state == 4) & 
               (Address.state_abbr << ['NY']) &
               (Address.state == 4))
         .aggregate_rows())

    # Return the users
    return list(query)

We now received the anticipated results of state: 4

Upvotes: 2

Related Questions