Add Query.get_all to query by list of IDs

[Marian Sigler]

Inspiration for this: http://stackoverflow.com/a/21267811/196244

Sometimes one has a list of IDs and needs to fetch the associated objects. There's two ways to do that:

session.query(Record).filter(Record.id.in_(seq)).all()

map(session.query(Record).get, seq)

Which one is better depends on whether most of the objects are already in the session or not. I assume it is trivial for SQLAlchemy to find out if this is the case, and choose the appropriate method.

Thus I propose adding a method Query.get_all that takes a list of IDs and returns the corresponding objects, guessing the better method to use. (Bonus: to allow functions using it to either accept a list of ids or of objects, allow the objects as input, too)

[Mike Bayer]

This is not something that can be part of the API.

1. the first approach does not support composite primary keys on most backends.

2. APIs should never be "guessing". the optimal approach here is to use an identity map lookup for all items that are locally present, then emit a single SELECT for the remainders (See #5)

3. arbitrary methods like "get_all()" are misleading, that there's some use case where it is deemed much more useful than other methods. It's not. If I need to work on a series of objects and have a local cache, I usually start out with:

   cache = dict(session.query(Record.id, Record))

4. note that most of the answers on the SO question are, "what's wrong with it like that?" I agree. Simplest is best. There's no advantage to approach # 2 unless all objects are in the session already.

5. Here's a recipe assuming single-column primary keys that does both as needed: http://stackoverflow.com/questions/444475/sqlalchemy-turning-a-list-of-ids-to-a-list-of-objects/28370511#28370511

[Marian Sigler]

Thanks a lot for your quick reply and the recipe at stackoverflow! I like the dict() trick, too!

I didn't think of the multicolumn primary key, that's right... Maybe I also overestimated the cost of re-fetching things we already have.

[Mike Bayer]

thanks for agreement ! :)