I have a structure like this:
A
└── B
└── C
`A` objects can have many `B` objects connected to them and a `B` objects
can be connected to many `A` objects. The same goes for `B` and `C`. As
they are all Many-to-Many, Association tables are used.
It is useful to use SQLAlchemy relationships to be able get a list of `B`
objects connected to a `C` for example. That is easy and accomplished in
the below code.
It is also useful to be able to do the same thing for indirect membership
between `A` and `C`. I.e. the list of `A` objects which are the parents of
`B` objects that are the parents of `C` objects. I can also accomplish this
with the more complicated relationship
a_list = relationship(
"A",
secondary="join(BCAssociation, B, BCAssociation.b_id ==
B.id).join(ABAssociation, ABAssociation.b_id == B.id)",
primaryjoin="C.id == BCAssociation.c_id",
secondaryjoin="A.id == ABAssociation.a_id",
viewonly=True, backref=backref('c_list')
)
That works perfectly. The code seems a little too complex to post in an
email so I have put it
here: https://gist.github.com/dpwrussell/02519dc06061fab0d89e
The result and query that is run when using that relationship is:
2015-07-31 14:27:41,631 INFO sqlalchemy.engine.base.Engine SELECT a.id
AS a_id, a.name AS a_name
FROM a, b_c_association JOIN b ON b_c_association.b_id = b.id JOIN
a_b_association ON a_b_association.b_id = b.id
WHERE ? = b_c_association.c_id AND a.id = a_b_association.a_id
2015-07-31 14:27:41,631 INFO sqlalchemy.engine.base.Engine (1,)
a1
The problem is if `A`, `B` and `C` are actually all extensions of the same
superclass. The output will be this:
2015-07-31 14:26:21,211 INFO sqlalchemy.engine.base.Engine SELECT
anon_1.object_type AS object_type, a.id AS a_id, anon_1.object_id AS
object_id, anon_1.object_name AS object_name
FROM object JOIN a ON object.id = a.id, b_c_association JOIN (SELECT
object.type AS object_type, object.id AS object_id, object.name AS
object_name, b.id AS b_id
FROM object JOIN b ON object.id = b.id) AS anon_1 ON
b_c_association.b_id = anon_1.b_id JOIN a_b_association ON
a_b_association.b_id = anon_1.b_id
WHERE ? = b_c_association.c_id AND a.id = a_b_association.a_id
2015-07-31 14:26:21,212 INFO sqlalchemy.engine.base.Engine (3,)
b1
It selects the `B` object, not the `A` object I was expecting. The full
code for that is also in the GitHub Gist. Just comment-out the 'Working
Example' and comment-in the 'Failing Example'.
I'm not sure if this is something that I should have mitigated in my
definition of the relationship (I can't figure out how) or if this is a
limitation of SQLAlchemy.
Also, at this time I think it's worth saying that SQLAlchemy is making the
bulk of what I wish to achieve very easy and because of that, I am getting
to these comparatively complicated requirements that were far down my TODO
list very quickly. So, thanks for that!
Thanks,
Douglas
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
