CODEWITHSUNDEEP
pythonsqlalchemy
frank
frank

Reputation: 1342

sqlalchemy: abusing temporary fields

I have a data structure that declares relationships like this (pseudocode):

class User:
    ...
class Rating:
    rater = User
    post = Post
class Post:
    ratings = hasmany(Rating)
    page_id = ...

I'm building a website using these models, and I'd I'm lazy, so I pass my template the current logged in User, and a bunch of Posts on the current page. My page needs to know what rating the logged in user gave to each Post, so I use SQLAlchemy's one-instance-per-session feature:

posts = session.query(Post).filter(Post.page_id==current_pageid)
ratings = session.query(Post, Rating)\
    .filter(Rating.rater==user.id)\
    .filter(Post.page_id==current_pageid)
for post in posts:
    post.user_rating = None # default value
for post, rating in ratings:
    post.user_rating = rating

Then I pass my template the posts list. Is this ugly awful practice? Can I do it some better way?

Upvotes: 1

Views: 237

Answers (2)

Denis Otkidach
Denis Otkidach

Reputation: 33250

Yes, it's bad practice. And it even might (in theory) beat you at some moment, e.g. when you query from the same session without clearing it for some post SQLAlchemy will return you the same cached object with already filled rating for some user unrelated to the current context. In practice it will work find in most cases.

Why not just pass a list of (post, rating) pairs to template? Most modern template engines available for Python can iterate over the list of pairs.

BTW, you can fetch both posts and ratings with single query (rating object will be None for OUTER JOIN when it's missing):

session.query(Post, Rating).select_from(Post)\
    .outerjoin(Rating, (Post.id==Rating.post_id) & (Rating.rater==…))\
    .filter(Post.page_id==…)

Upvotes: 1

van
van

Reputation: 77012

What you are doing is good enough, except that your query is lacking a WHERE clause between the Post and Rating: 

# ...
.filter(Post.id==Rating.post_id)\

But you can also get the result in one query:

qry =  (session.query(Post, Rating).
        outerjoin(Rating, and_(Post.id==Rating.post_id, Rating.user_id==user.id)).
        filter(Post.page_id==current_pageid)
        )
res = qry.all() # you can return *res* already to a view, but to get to your results, do below as well:
for post, rating in res:
    post.user_rating = rating
posts = [post for post, rating in res]
return posts

Note that in your case posts is not really a list, but a query, and if you iterate over it second time, you might lose the user_rating attribute. You should be cautious returning session-bound objects like query to a view. It is safer to return lists like in the same code above. To fix your code, just add .all() to the query:

posts = session.query(Post).filter(Post.page_id==current_pageid).all()

Upvotes: 1

Related Questions