On Aug 31, 2010, at 4:41 PM, cd34 wrote:
> 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) ?
you do the join with your query(), query.join(Job.user_job_progress), or
query.join((User_job_progress, User_job_progress.job_id==Job.job_id)),
then filter with User_job_progress.fb_uid since that's your user_id, or join
again to User if you need something there, whichever. If you wanted to join
with Job.user_job_progress, you'd configure that as a relationship().
>
> If I were doing this without an ORM, progress would be a LEFT JOIN
> since they may not have a progress record.
OK, then query.outerjoin()....
--
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.
