I have 4 tables: users, jobs, job_items, job_progress
class User(Base):
__tablename__ = 'xxx_users'
fb_uid = Column(mysql.MSBigInteger(20, unsigned=True),
primary_key=True)
class Job(Base):
__tablename__ = 'xxx_jobs'
job_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True)
descr = Column(Unicode(80))
items = relation('Job_items')
class Job_items(Base):
__tablename__ = 'xxx_job_items'
ji_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True)
job_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey(Job.job_id))
item_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey(Item.item_id))
quantity = Column(mysql.MSBigInteger(20, unsigned = True))
use_item = Column(mysql.MSEnum('Y','N'), default = 'N')
item = relation('Item', uselist=False)
class User_job_progress(Base):
__tablename__ = 'xxx_user_job_progress'
id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True)
fb_uid = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey(User.fb_uid))
job_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey(Job.job_id))
The problem I'm trying to solve:
tmpl_context.jobs =
meta.Session.query(Job).order_by(Job.min_level).order_by(Job.descr).filter(Job.tier==1).all()
This allows me to get the Job and Job_item data, but, I can't figure
out how to put the job progress into the query since it is based on
the fb_uid and the job_id.
I'm thinking something like
progress = relation('User_job_progress', secondary=User,
uselist=False)
but, I need to pass the parameter of fb_uid to the relation. Do I set
this as an additional filter? filter(User.fb_uid==1) ?
If I were doing this without an ORM, progress would be a LEFT JOIN
since they may not have a progress record. I'm thinking the
alternative would be to relate the job progress off the user which
would relate to the users, but then I need to do two queries. I'm
thinking I've missed something somewhere. Is this a case for
_polymorphic? or is there a way to pass a condition to a relation
dynamically? I could also build the query rather than having the
relation set and do the join manually.
Any thoughts or can you point me in the right direction? I've read
the docs but, haven't seen any example that is similar to what I am
trying to do.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
