On Nov 9, 2012, at 10:55 AM, Benjamin Sims wrote:
> Hi,
>
> We have a relationship that looks something like this:
>
> User 1 - * Posts
>
> Posts can have various states, let's say 'DRAFT', 'LIVE', 'DELETED'.
>
> Sometimes, we want to get all of the posts in existence for a user by doing:
>
> user.all_posts
>
> Sometimes, we just want to get posts that are not deleted:
>
> user.open_posts
>
> We opted to do this by specifying the primary_join on the relationship, thus:
>
> open_posts = relationship("Post", = "and_(User.id==Post.user_id,"ยท
> "or_(Post.status=='DRAFT',
> Post.status=='LIVE'))")
>
> all_posts = relationship("Post")
>
> (as suggested here:
> http://stackoverflow.com/questions/2863786/how-do-i-specify-a-relation-in-sqlalchemy-where-one-condition-requires-a-column)
>
> This worked, but we now have a problem in some of our processing - when we
> use user.open_posts before changes have been sent to the database, all posts
> including those deleted are returned.
>
> Putting in session.refresh at various points now seems to do the trick, but I
> wanted to ask:
>
> 1. Is our approach generally correct as a way of doing filtered
> relationships/joins
> 2. Are session.refresh calls the right way to make it work while changes are
> still in memory
better ways:
1. put a @validates listener on Post, such that when the "status" flag changes,
it looks at the parent User object's "open_posts" collection and removes
itself. open_posts should be viewonly=True so the history events shouldn't be
an issue (and if they are, you can use
sqlalchemy.orm.attributes.set_committed_value() to re-set a new collection with
the item removed).
2. use @property and/or @hybrid_property for open_posts, and evaulate the
"open_posts" collection as a simple in-Python subset of "user.all_posts" when
invoked in Python. This is probably how I'd do it.
3. If you truly want to hit the DB again if a Post.status has changed, then
still use events like @validates on Post.status, but instead of using
"refresh()" use "expire()" on Post.open_posts, so that SQL isn't emitted if and
until the Post.open_posts collection is actually accessed.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
