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 [email protected] <javascript:>
> > > <mailto:[email protected] <javascript:>
> <javascript:>>.
> > > To post to this group, send email to [email protected]
> > <javascript:>
> > > <mailto:[email protected] <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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
