[sqlalchemy] Complex query (for me)

2011-01-26 Thread Enrico Morelli 
Dear all,

I've a situation where some tutors has some doctorates. Each doctorate
has to upload some reports. Each tutor has to approve reports of his
doctorates.

These are the tables and mappers:

members_table = Table('members', metadata,
Column('id', types.Integer, primary_key=True),
Column('lastname', types.Unicode(30), nullable=False),
Column('tutor_id', types.Integer, ForeignKey('members.id'),
nullable=True),
Column('removed', types.Boolean, default=False))

reports_table = Table('reports', metadata,
Column('id', types.Integer, primary_key=True),
Column('approved', types.Boolean, default=False),
Column('writer', types.Integer, ForeignKey('members.id'),
nullable=False))

mapper(Members, members_table,
   properties={
   'tutor': relation(Members, backref='doctorate',
 remote_side=members_table.c.id) 
   })

mapper(Reports, reports_table,
properties={
'owner': relation(Members, backref='report')
})

I have to create a query to select all reports of all doctorates of a
tutor. Using the following query I'm able to have all doctorate of a
tutor. 
result = Session.query(Members).filter(and_(Members.removed==False,
Members.tutor.has(Members.id==tutor_id))).all()

But I'm not able to select also the doctorate reports.

Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.

Re: [sqlalchemy] Complex query (for me)

2011-01-26 Thread Enrico Morelli 
For the moment I solved using these query:

doctorate = Session.query(Members).filter(and_(Members.removed==False,
Members.tutor.has(id=tutor_id))).subquery()
reports = Session.query(Reports, doctorate.c.id).outerjoin((doctorate,
Reports.writer==doctorate.c.id)).order_by(Reports.id).all()
# Only to obtain the objects related to the tutor
reports = [reports[i][0] for i in range(len(reports)) if reports[i][1]]

Are there other possibilities?

Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.