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.
(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.
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.
from sqlalchemy import Column, Integer, ForeignKey, String, DateTime
from sqlalchemy import and_, func
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import arrow
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent_objs'
id = Column(Integer, primary_key=True)
obj_type = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('user_objs.id'))
time = Column(DateTime)
__mapper_args__ = {
'polymorphic_on': obj_type,
'with_polymorphic': '*'
}
class Child(Parent):
__tablename__ = 'child_objs'
id = Column(Integer, ForeignKey('parent_objs.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'child',
}
class NonInherited(Base):
__tablename__ = "not_inherited_objs"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user_objs.id'))
time = Column(DateTime)
class User(Base):
__tablename__ = 'user_objs'
id = Column(Integer, primary_key=True)
class UserEvent(Base):
__tablename__ = 'user_events'
id = Column(Integer, primary_key=True)
time = Column(DateTime)
user_id = Column(Integer, ForeignKey('user_objs.id'))
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = Session()
twenty_hours_ago = arrow.utcnow().replace(hours=-20).naive
ten_hours_ago = arrow.utcnow().replace(hours=-10).naive
just_after_ten_hours_ago = arrow.utcnow().replace(hours=-10, seconds=+1).naive
five_hours_ago = arrow.utcnow().replace(hours=-5).naive
just_after_five_hours_ago = arrow.utcnow().replace(hours=-5, seconds=+1).naive
now = arrow.utcnow().naive
u = User(id=1)
session.add(u)
session.commit()
ue_1 = UserEvent(id=1, user_id=u.id, time=twenty_hours_ago)
ue_2 = UserEvent(id=2, user_id=u.id, time=ten_hours_ago)
ue_3 = UserEvent(id=3, user_id=u.id, time=five_hours_ago)
ue_4 = UserEvent(id=4, user_id=u.id, time=now)
child_1 = Child(id=1, time=just_after_ten_hours_ago, user_id=u.id)
child_2 = Child(id=2, time=just_after_five_hours_ago, user_id=u.id)
non_inherited_1 = NonInherited(id=1, time=just_after_ten_hours_ago, user_id=u.id)
non_inherited_2 = NonInherited(id=2, time=just_after_five_hours_ago, user_id=u.id)
map(session.add, [ue_1, ue_2, ue_3, ue_4, child_1, child_2, non_inherited_1, non_inherited_2])
session.commit()
parent_table = Parent.__table__
q_with_raw_table = (
session.query(parent_table.c.id, UserEvent.id)
.join(User, parent_table.c.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_table.c.time)
.correlate(User)
.correlate(parent_table)
.as_scalar()
))
)
q_with_declarative = (
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)
.correlate(User)
.correlate(Parent)
.as_scalar()
))
)
q_with_non_inherited_table = (
session.query(NonInherited.id, UserEvent.id)
.join(User, NonInherited.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 <= NonInherited.time)
.correlate(User)
.correlate(NonInherited)
.as_scalar()
))
)
print q_with_raw_table.all()
print q_with_non_inherited_table.all()
print q_with_declarative.all()
