Peewee 1:n mapping duplicates in join

Question

I have some peewee models with 1:n mapping and backrefs, like so:

class Userr(BaseModel):
    rowId = BigAutoField(primary_key=True)
    userId = CharField()

class UserName(BaseModel):
    rowId = BigAutoField(primary_key=True)
    name = TextField()
    user = ForeignKeyField(Userr, backref='userNames')

class UserAddress(BaseModel):
    rowId = BigAutoField(primary_key=True)
    address = TextField()
    user = ForeignKeyField(Userr, backref='userAddresses')

I want to write a query where I can get all Userrs with their UserNames and UserAddresses

users = Userr.select().join(UserName).switch(Userr).join(UserAddress)

But I get duplicate rows with this. When I have one Userr with 2 UserNames, I get 2 rows, where each Userr.userNames (the backref) contains both the UserNames. How can I avoid these duplicates?

Answer 1

You can use .distinct(), e.g.

users = Userr.select().join(...).distinct()

If you want to pre-fetch all their related addresses and user-names, then you can try using prefetch():

users = prefetch(Userr, UserName, UserAddress)

See the docs: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#list-users-and-all-their-tweets