I have a three tier graph of data created declaratively with:
class L3Acct(BASE):
__tablename__ = 'l3_acct'
__table_args__ = (UniqueConstraint("l2_id", "l3_acct"), {})
id = Column(Integer, primary_key=True)
l2_id = Column(ForeignKey('l2_acct.id'))
l3_acct = Column(Integer)
... (additional fields)
class L2Acct(BASE):
__tablename__ = 'l2_acct'
__table_args__ = (UniqueConstraint('l1_id', 'l2_acct'), {})
id = Column(Integer, primary_key=True)
l1_id = Column(ForeignKey('l1_acct.id'))
l2_acct = Column(Integer)
description = Column(String(50))
l3_acct = relation(L3Acct, lazy=False, backref=backref('l2'),
collection_class=set,
cascade="all, delete, delete-orphan")
class L1Acct(BASE):
__tablename__ = 'l1_acct'
id = Column(Integer, primary_key=True)
l1_acct = Column(Integer, unique=True)
description = Column(String(50))
l2_acct = relation(L2Acct, lazy=False, backref=backref('l1'),
collection_class=set,
cascade="all, delete, delete-orphan")
I would like to be able to select:
1) all rows
2) all rows with a give L1Acct.id
or
3) all rows with a given L1Acct.id and a given L2Acct.id
def get_accounts_list(session, l1_id=None, l2_id=None):
q = session.query(L1Acct)
if l1_id is not None:
q = q.filter(L1Acct.id==l1_id)
if l2_id is not None:
q = q.filter(L2Acct.id==l2_id)
This seems to work properly for cases 1 and 2, but not for case 3. If
I print q at this point for option 3 here's the result:
SELECT l1_acct.id AS l1_acct_id, l1_acct.l1_acct AS l1_acct_l1_acct,
l1_acct.description AS l1_acct_description, <more columns>
FROM l2_acct, l1_acct
LEFT OUTER JOIN l2_acct AS l2_acct_1 ON l1_acct.id = l2_acct_1.l1_id
LEFT OUTER JOIN l3_acct AS l3_acct_1 ON l2_acct_1.id =
l3_acct_1.l2_id
WHERE l1_acct.id = :id_1 AND l2_acct.id = :id_2
>From my understanding of SQL the problem may be the inclusion of
l2_acct in the FROM clause.
Any suggestions, or am I trying something too complex for SQLAlchemy's
ORM?
--
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.
