Query from a list of related SQLalchemy and Flask

Question

I have a User that has-one Person . So User.person is Person .
I am trying to get the User list from the Person list.

I tried the following:

 >>> people = Person.query.filter().limit(3) <flask_sqlalchemy.BaseQuery object at 0x111c69bd0> >>> User.query.filter(User.person.in_(people)).all() NotImplementedError: in_() not yet supported for relationships. For a simple many-to-one, use in_() against the set of foreign key values. 

What is the best way to get the User list, where User.person is on this people list?

Answer 1

As the error message tells you, you need to use in_ instead of foreign keys:

 User.query.join(User.person).filter(Person.id.in_(p.id for p in people)).all() 

Since you are going to request both in any case, it might be better to do a joint download and then get people to use Python:

 people = Person.query.join(Person.user).options(db.contains_eager(Person.user)).limit(3).all() users = [p.user for p in people]