Passing runtime value to relationship join condition

[cd34]

I'm trying to pass a value to a relation at runtime rather than
instantiation time. I've got the callable working with a fixed value,
but, in reading the docs over the last few days , I'm either not
finding the proper incantation or the right terms in the documentation
to figure out what I'm trying to do.

The barest example code I can write that demonstrates what I'm trying
to do:

#!/usr/bin/python2.6

import sqlalchemy

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Unicode
from sqlalchemy import ForeignKey
from sqlalchemy import and_

from sqlalchemy.orm import relationship
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import backref

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)
metadata = Base.metadata

class AuthUser(Base):
__tablename__ = 'auth_users'

id = Column(sqlalchemy.Integer(unsigned=True), primary_key=True,
autoincrement=True)

class NewsVoteLog(Base):
__tablename__ = 'news_vote_log'

id = Column(sqlalchemy.Integer(unsigned=True), primary_key=True,
autoincrement=True)
user_id = Column(sqlalchemy.Integer(unsigned=True),
ForeignKey(AuthUser.id))
ncid = Column(sqlalchemy.Integer( unsigned=True),
ForeignKey('news_comments.id'))

def __repr__(self):
return 'NewsVoteLog <id: %s user_id: %s ncid: %s>' %
(self.id,
self.user_id, self.ncid)

def callable_user_id():
return 1

class NewsComment(Base):
__tablename__ = 'news_comments'

id = Column(sqlalchemy.Integer(unsigned=True), primary_key=True,
autoincrement=True)
parent_id = Column(sqlalchemy.Integer(unsigned=True),
ForeignKey('news_comments.id'), default=0)
title = Column(Unicode(120))

#votes = relationship('NewsVoteLog',
backref=backref('news_vote_log'), remote_side=NewsVoteLog.ncid)
votes = relationship('NewsVoteLog',
backref=backref('news_vote_log'),
primaryjoin=and_(NewsVoteLog.ncid==id,
NewsVoteLog.user_id==callable_user_id),
foreign_keys=[NewsVoteLog.ncid, NewsVoteLog.user_id])

metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)()

user = AuthUser()
DBSession.add(user)
user2 = AuthUser()
DBSession.add(user2)
user3 = AuthUser()
DBSession.add(user3)

news = NewsComment(parent_id=0, title='test article')
DBSession.add(news)

DBSession.flush()

vote1 = NewsVoteLog(user_id=user.id, ncid=news.id)
DBSession.add(vote1)
vote2 = NewsVoteLog(user_id=user2.id, ncid=news.id)
DBSession.add(vote2)
vote3 = NewsVoteLog(user_id=user3.id, ncid=news.id)
DBSession.add(vote3)


DBSession.flush()

#test_record =
DBSession.query(NewsComment).filter(NewsComment.id==1).one()
test_record =
DBSession.query(NewsComment).filter(NewsComment.id==1).params(uid=1).one()
print test_record.votes


What I would like to do is pass uid at runtime. I hacked it together
with an outerjoin, but, as the child records are a relation from the
parent, only the parent received the outerjoin.

If someone can point me to the documentation or name the concept I'm
looking for, I'd appreciate it. Thanks.

[Michael Bayer]

What's making this hard to follow is that "uid" is only referenced in your Query below and I don't see any other mention of what "uid" is.

There's a recipe for the general idea of passing binds to relationship at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter . The elaborate side of it is when the relationship is lazily loading, you have to store your bind parameter in the lazyloader itself, hence the TemporalLoader class in that recipe.

>
> If someone can point me to the documentation or name the concept I'm
> looking for, I'd appreciate it. Thanks.
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[cd34]

uid was the logged in user's id. I didn't grasp the bindparam/
eagerload dependency and almost had that solution working. That wiki
page clarified it.

As always, thank you for the quick response.