CODEWITHSUNDEEP
ormselectdictionarysqlalchemy
zizzamia
zizzamia

Reputation: 241

ORM SQLAlchemy: how to build a select/where query from a list/dict with hundreds of elements

I have two tables: "user" which is a list of users of the website and "post" which is a list of all the users' posts. Any user follows different users, like Twitter. Any 'user' object holds a 'following' dict attribute containing a list of id. Any 'post' object holds a 'user_id' label containing a id user.

I want to obtain a query that, from the table 'Post', collect all the posts with 'user_id' matching the id's contained in the dict.

But such a query, translated straight, would result in a SELECT with hundreds of WHERE id= 1 AND id=2 etc in a row. Is there a better way?

Upvotes: 0

Views: 329

Answers (1)

van
van

Reputation: 76992

It is not clear how your following dict attribute is persisted. If it were stored in a separate N-1 table Follows, assuming your db schema looks like:

User(UserID, ...)
Post(PostID, UserID, ...)
Follows(UserID, FollowsUserID, ...)

the SQL query to retrieve the data you want would be:

SELECT  u.UserID,
        p.*
FROM    User u
JOIN    Follows f
    ON  u.UserID = f.UserID
JOIN    Post p
    ON  f.FollowsUserID = p.UserID

Assuming the objects and relations are configured, the SA query to do the same job would like similar to:

u = ... # get desired User object
qry = (session.query(Post).
        join(User). # User will join to Post properly
        join(Follows, Follows.FollowsUserID == User.UserID). # specify the join condition
        filter(Follows.User == u)
      )

Upvotes: 1

Related Questions