On 7/31/15 2:51 PM, Douglas Russell wrote:
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
OK well first off when diagnosing these subquery-heavy mappings, the
first thing I strongly suggest is to not do your initial dev/testing
against SQLite. There is an extra layer of query rewriting in SQLite to
work around the fact that SQLite cannot accommodate right-nested joins,
and that's why you see that JOIN (SELECT ...) thing happening.
Running it on any other database, I usually default to Postgresql
because it has the most solid and consistent query parser, the
still-incorrect query is a little easier to read and it correctly
reports the issue as that the "object" table is mentioned twice:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) table name
"object" specified more than once
[SQL: 'SELECT object.type AS object_type, a.id AS a_id, object.id AS
object_id, object.name AS object_name \nFROM object JOIN a ON object.id
= a.id, b_c_association JOIN (object JOIN b ON object.id = b.id) ON
b_c_association.b_id = b.id JOIN a_b_association ON a_b_association.b_id
= b.id \nWHERE %(param_1)s = b_c_association.c_id AND a.id =
a_b_association.a_id'] [parameters: {'param_1': 3}]
The SQLite version clearly is getting confused because SQLAlchemy's
query rewriting it tripping over that double object table thing but not
raising an error.
So we need to use aliasing to get that secondary table inside of a
sub-namespace. There are probably slicker ways to work it out with
declarative but just to make it work you can build up the join + alias
and the relationship after the fact:
secondary = join(BCAssociation, B, BCAssociation.b_id == B.id).\
join(ABAssociation, ABAssociation.b_id == B.id).alias(None)
C.a_list = relationship(
"A",
secondary=secondary,
primaryjoin=C.id == secondary.corresponding_column(BCAssociation.c_id),
secondaryjoin=A.id ==
secondary.corresponding_column(ABAssociation.a_id),
viewonly=True,
backref=backref('c_list')
)
then on PG we get:
SELECT object.type AS object_type, a.id AS a_id, object.id AS object_id,
object.name AS object_name
FROM (SELECT b_c_association.b_id AS b_c_association_b_id,
b_c_association.c_id AS b_c_association_c_id, object.type AS
object_type, object.id AS object_id, object.name AS object_name, b.id AS
b_id, a_b_association.a_id AS a_b_association_a_id, a_b_association.b_id
AS a_b_association_b_id
FROM b_c_association JOIN (object JOIN b ON object.id = b.id) ON
b_c_association.b_id = b.id JOIN a_b_association ON a_b_association.b_id
= b.id) AS anon_1, object JOIN a ON object.id = a.id
WHERE %(param_1)s = anon_1.b_c_association_c_id AND a.id =
anon_1.a_b_association_a_id
2015-07-31 15:19:57,066 INFO sqlalchemy.engine.base.Engine {'param_1': 3}
a1
you can see the right-nested join in there that SQLite can't do, so on
SQLite the translation gives us:
SELECT object.type AS object_type, a.id AS a_id, object.id AS object_id,
object.name AS object_name
FROM (SELECT b_c_association.b_id AS b_c_association_b_id,
b_c_association.c_id AS b_c_association_c_id, anon_2.object_type AS
object_type, anon_2.object_id AS object_id, anon_2.object_name AS
object_name, anon_2.b_id AS b_id, a_b_association.a_id AS
a_b_association_a_id, a_b_association.b_id AS a_b_association_b_id
FROM 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_2 ON
b_c_association.b_id = anon_2.b_id JOIN a_b_association ON
a_b_association.b_id = anon_2.b_id) AS anon_1, object JOIN a ON
object.id = a.id
WHERE ? = anon_1.b_c_association_c_id AND a.id = anon_1.a_b_association_a_id
2015-07-31 15:21:14,577 INFO sqlalchemy.engine.base.Engine (3,)
a1
you can see its the same thing but the right-nested JOIN is blown up
into a new SELECT (very difficult logic to make that work in most cases).
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
