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 AS thing_id,
thing.type_id AS thing_type_id, thing.text AS thing_text, (SELECT
type.type \nFROM type, thing \nWHERE 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 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 = 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 = 'junk';
but better again would be, this, right?
SELECT 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 = 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 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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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.
