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
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].
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.
