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:>>.
> 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+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.
--
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.
