Great, the aliasing works. Thanks so much. But, if I want to have my design scale, how can I ask sqlalchemy to use a JOIN rather than a subquery?
On Thursday, February 9, 2017 at 10:38:42 AM UTC-6, Mike Bayer wrote: > > Running your example with > > 'polymorphic_on': > select([Type.type]).where(Type.id == type_id).as_scalar() > > > "res = s.query(Stuff).all()" works fine. > > The reason "res = s.query(Thing).join(Type).filter(Type.type == > 'stuff').all()" fails is that Type is being injected into the SELECT > statement twice and it's confused. > > the solution is to protect your subquery from interference by using an > alias: > > type_alias = aliased(Type) > > > class Thing(Base): > __tablename__ = 'thing' > > id = Column(Integer, primary_key=True) > type_id = Column(Integer, ForeignKey(Type.id), nullable=False) > > type = relationship(Type) > > __mapper_args__ = { > 'polymorphic_on': > select([type_alias.type]).where(type_alias.id == > type_id).as_scalar(), > 'with_polymorphic': '*' > } > > > then the join example at the end produces: > > SELECT thing.id AS thing_id, thing.type_id AS thing_type_id, (SELECT > type_1.type > FROM type AS type_1 > WHERE type_1.id = thing.type_id) AS _sa_polymorphic_on, thing.stuff AS > thing_stuff, thing.junk AS thing_junk > FROM thing INNER JOIN type ON type.id = thing.type_id > WHERE type.type = %(type_2)s > > noting that this query is not going to scale up to millions of rows that > well as MySQL hates those subqueries. > > > > > > > > On 02/09/2017 11:09 AM, Shane Carey wrote: > > That is what I initially had, > > > > I get the error > > > > sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT type.type > > FROM type, thing > > WHERE type.id = thing.type_id' returned no FROM clauses due to > > auto-correlation; specify correlate(<tables>) to control correlation > > manually. > > > > On Thursday, February 9, 2017 at 10:02:40 AM UTC-6, Mike Bayer wrote: > > > > > > > > On 02/09/2017 10:14 AM, Shane Carey wrote: > > > Hi, another question. > > > > > > I set my discriminator on the surrogate primary key of its table. > > > However, when I query for all of a certain discriminator, I get an > > error > > > 'Multiple rows returned for subquery'. > > > > > > Here is my canonical example > > > > > > from sqlalchemy import * > > > from sqlalchemy import select, and_, event, inspect > > > from sqlalchemy.orm import * > > > from sqlalchemy.ext.declarative import * > > > > > > Base = declarative_base() > > > > > > class Type(Base): > > > __tablename__ = 'type' > > > > > > id = Column(Integer, primary_key=True) > > > type = Column(String(8), unique=True) > > > > > > class Thing(Base): > > > __tablename__ = 'thing' > > > > > > id = Column(Integer, primary_key=True) > > > type_id = Column(Integer, ForeignKey(Type.id), nullable=False) > > > > > > type = relationship(Type) > > > > > > __mapper_args__ = { 'polymorphic_on': > > select([Type.type]).where(Type.id > > > == type_id).correlate(Type).as_scalar(), 'with_polymorphic': '*' } > > > > > > @event.listens_for(Thing, 'init', propagate=True) > > > def set_identity(instance, *args, **kwargs): > > > instance.type_id = select([Type.id]).where(Type.type == > > > object_mapper(instance).polymorphic_identity) > > > > > > class Stuff(Thing): > > > stuff = Column(String(8)) > > > __mapper_args__ = { 'polymorphic_identity': 'stuff' } > > > > > > class Junk(Thing): > > > junk = Column(String(8)) > > > __mapper_args__ = { 'polymorphic_identity': 'junk' } > > > > > > if __name__ == '__main__': > > > e = create_engine('mysql+pymysql://user:password@localhost/test', > > echo=True) > > > > > > Base.metadata.drop_all(e) > > > Base.metadata.create_all(e) > > > > > > s = Session(e) > > > > > > s.add_all([Type(type='stuff'), Type(type='junk')]) > > > for i in range(10): > > > s.add(Stuff(stuff='stuff_{}'.format(i))) > > > s.add(Junk(junk='junk_{}'.format(i))) > > > > > > s.commit() > > > > > > res = s.query(Thing).join(Type).filter(Type.type == 'stuff').all() > > > > > > #res = s.query(Stuff).all() Also fails > > > > > > At first I was receiving the error 'No FROM table specified due to > > > autocorrelate', but I was able to solve that with > 'polymorphic_on': > > > select([Type.type]).where(Type.id == > > type_id).correlate(Type).as_scalar() > > > which I still do not fully understand. The exact error I get is > > > > I think you want to correlate(Thing) there. correlate(X) means you > > don't want "X" in the FROM list in the subquery. But also the > > correlation should be automatic here so you wouldn't need to refer > to > > "Thing" in the class def (if you did, you'd need to turn > > __mapper_args__ > > into a @declared_attr). > > > > that is: > > > > "polymorphic_on": select([Type.type]).where(Type.id == > > type_id).as_scalar() > > > > should work > > > > > > > > > > > > sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1242, > > > 'Subquery returns more than 1 row') [SQL: 'SELECT thing.id > > <http://thing.id> AS thing_id, > > > thing.type_id AS thing_type_id, thing.text AS thing_text, (SELECT > > > type.type \nFROM type, thing \nWHERE type.id <http://type.id> = > > thing.type_id) AS > > > _sa_polymorphic_on, thing.stuff AS thing_stuff, thing.junk AS > > thing_junk > > > \nFROM thing'] > > > > > > it seems like the SQL it should generate is > > > > > > SELECT thing.id <http://thing.id> AS thing_id, > > > thing.type_id AS thing_type_id, > > > thing.text AS thing_text, > > > ( > > > SELECT type > > > FROM type # this was thing before > > > WHERE type.id <http://type.id> = thing.type_id > > > ) AS _sa_polymorphic_on, > > > thing.stuff AS thing_stuff, > > > thing.junk AS thing_junk > > > FROM thing > > > INNER JOIN type ON type.id <http://type.id> = thing.type_id > > > WHERE type.type = 'junk'; > > > > > > but better again would be, this, right? > > > > > > SELECT thing.id <http://thing.id> AS thing_id, > > > thing.type_id AS thing_type_id, > > > thing.text AS thing_text, > > > type.type AS _sa_polymorphic_on, > > > thing.stuff AS thing_stuff, > > > thing.junk AS thing_junk > > > FROM thing > > > INNER JOIN type ON type.id <http://type.id> = thing.type_id > > > WHERE type.type = 'junk'; > > > > > > so I am not sure what is going wrong or how I can generate the > > correct SQL, > > > or if possible generate a join rather than a subquery. > > > > > > Thanks for you help and patience > > > > > > -- > > > SQLAlchemy - > > > The Python SQL Toolkit and Object Relational Mapper > > > > > > http://www.sqlalchemy.org/ > > > > > > To post example code, please provide an MCVE: Minimal, Complete, > and > > > Verifiable Example. See http://stackoverflow.com/help/mcve > > <http://stackoverflow.com/help/mcve> for a full > > > description. > > > --- > > > 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+...@googlegroups.com <javascript:> > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:> > <javascript:>>. > > > To post to this group, send email to sqlal...@googlegroups.com > > <javascript:> > > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > > Visit this group at https://groups.google.com/group/sqlalchemy > > <https://groups.google.com/group/sqlalchemy>. > > > For more options, visit https://groups.google.com/d/optout > > <https://groups.google.com/d/optout>. > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.