0

I am using SQL Alchemy and I want to return a list of Document Ids. The Ids are the primary key in the documents table. My current query returns a list of tuples.

userDocs = session.query(Document.idDocument).filter(Document.User_idUser == user.idUser).all()

The reason I want a list of ids is so that I can search another table using in_(userDocs).

So another solution would be to be able to search using tuples. I am currently returning nothing from my second query using userDocs.

Thank you!!

Jeremy Thiesen
  • 167
  • 2
  • 14

1 Answers1

2

You don't need to do an intermediate query, you can do this all in one shot!

things = session.query(Things) \
                .join(Thing.documents) \
                .filter(Document.User_idUser==user.idUser)

You just query on the properties of the Document through its relationship() on the intended entity.

SingleNegationElimination
  • 151,563
  • 33
  • 264
  • 304
  • I agree with @TokenMacGuy: you should just extend your (second) query by adding a `join` to the `Document` table and adding that `filter` to it. In this way you will get your results in one query (one `SQL` statement). Even if you do not have a `relationship` between `Thing` and `Document`, you can still `join` with explicit condition. Not to mention that the `IN` operator might be sub-optimal in general (http://stackoverflow.com/questions/1013797/is-sql-in-bad-for-performance) – van Apr 12 '12 at 14:07
  • Thank you. Perfect, wasn't thinking of using its relationships to filter. I was trying some experimentation with nested queries that didn't turn out well, but this seems to be the best way. Thanks! – Jeremy Thiesen Apr 13 '12 at 01:30
  • i think you mean query(Things).join(Thing.documents).filter(Document.User_idUser==user.idUser), you can't navigate into the next entity directly from Thing.documents (SQLA won't implicitly join) – zzzeek Apr 26 '12 at 17:28