On Sep 24, 2010, at 3:54 PM, Microcomputer Enhancement wrote:
> 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.
1. all the LEFT OUTER JOINs you see there are because of the lazy=False you
have configured. This doesn't really change the result but its usually easier
to leave lazy as "True", and just use options() to "joinedload" collections you
may want in fewer steps than the normal route, but only after you get the
correct results without any joined loading. It would certainly simplify
reading the query until you get it to do what you want.
2. when you want to select from table A, B, C, you need to join the tables
together, either by equating them in the filter() clause or by using
query.join().
so like:
L1.id
-----
1
2
3
L2.id L2.l1_id
----------------------
1 1
2 1
3 2
L3.id L3.l2_id
-----------------------
1 1
2 2
3 2
query(L1, L2, L3).filter(L1.id==L2.l1_id).filter(L2.id==L3.l2_id)
gives you
L1.id L2.id L2.l1_id L3.id L3.l2_id
-----------------------------------------------------
1 1 1 1 1
1 2 1 2 2
1 2 1 3 2
you don't get L1.id==3 because no L2.l1_id references 3. You don't get
L2.id==3 because no L3.l2_id references 3. You don't get L1.id==2 because you
aren't getting L2.id #3.
Then you add the additional filter() limit L1, L2, L3 accordingly.
join would be:
query(L1, L2, L3).join(L1.l2_acct).join(L2.l3_account)
same thing.
>
> 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.
>
--
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.
