On 02/24/2016 08:24 PM, Andrew Wansley wrote:
Hey y'all,
I'm trying to understand the interaction between joined table
inheritance and a correlated subquery.
The query seems to work as I expect if the same table doesn't have a
joined table subclass, or if I circumvent the polymorphic code by
querying Parent.__table__ directly, but not if I query the declarative
class directly.
Roughly, we want to query some kind of user action, joining in the most
immediately preceding event for the user who performed that action. We
select the event with a correlated subquery.
See the attached file for an isolated test case.
you did a fantastic job giving me exactly the kind of test case I'm
looking for (minus having to install "arrow" :) ) but yeah this
doesn't work as it should. I'm not a fan of across-the-board
with_polymorphic at the mapper level because it generates atrocious
queries, so you aren't doing it "wrong" but you might want to consider
using ad-hoc with_polymorphic() when you need it.
Anyway I can give you two workarounds and a bug report, bug report is
here:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3662/correlation-fails-with-with_polymorphic
workarounds are as follows:
# workaround one - use Core correlate_except() to grab every
# possible table that's not UserEvent
q_with_declarative_two = (
session.query(Parent.id, UserEvent.id)
.join(User, Parent.user_id == User.id)
.join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
.filter(UserEvent.time <= Parent.time)
.as_scalar().correlate_except(UserEvent)
))
)
# workaround two - use ad-hoc with_polymorphic to turn off
# the mapper-level polymoprhic
from sqlalchemy.orm import with_polymorphic
parent_poly = with_polymorphic(Parent, [], Parent.__table__)
q_with_declarative_three = (
session.query(parent_poly.id, UserEvent.id)
.join(User, parent_poly.user_id == User.id)
.join(UserEvent, and_(
UserEvent.user_id == User.id,
UserEvent.time == session.query(
func.max(UserEvent.time)
).filter(UserEvent.user_id == User.id)
.filter(UserEvent.time <= parent_poly.time)
.correlate(parent_poly).correlate(User)
.as_scalar()
))
)
I'll have to find time to look into the issue here, these
with_polymorphic things represent the absolute most confusing bugs to
work out, there may be a 1.0-level fix that can come out but I'd have to
look and see how big a change is needed.
(Tested on SqlAlchemy 1.0.12 with Postgres 9.3.5 as well as Sqlite 2.6.0)
I'm just as happy to hear "You're doing it wrong! Here's how to re-write
your query" as I am to a clear explanation of why this difference exists..
--
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
