On 02/09/2017 01:54 PM, Shane Carey wrote:
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?
for the polymorphic identity the way you're doing, there's not really a
portable way to make it do a join every time...maybe if you loaded the
JOIN into a SELECT and set that as a second argument into
with_polymorphic for every subclass, as with_polymorphic allows an
alternate selectable to be specified as well, though that is getting a
little crazy.
It's a lot simpler if you can just pre-load that list of types up front
and using the integer identifier inside something like a CASE statement.
Then you don't need any joins or anything.
Looks like:
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__ = {
'with_polymorphic': '*'
}
@classmethod
def _populate_polymorphic(cls, items):
inspect(cls)._set_polymorphic_on(
case(
[
(
cls.type_id == literal_column(str(elem_id)),
literal_column("'%s'" % elem_name)
)
for elem_id, elem_name in items
]
)
)
cls._polymorphic_lookup = dict(
(elem_name, elem_id) for elem_id, elem_name in items
)
@event.listens_for(Thing, 'init', propagate=True)
def set_identity(instance, *args, **kwargs):
instance.type_id = instance._polymorphic_lookup[
object_mapper(instance).polymorphic_identity]
then:
Thing._populate_polymorphic(s.query(Type.id, Type.type).all())
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
<http://type_alias.id> ==
type_id).as_scalar(),
'with_polymorphic': '*'
}
then the join example at the end produces:
SELECT thing.id <http://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 <http://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 <http://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 <http://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>
> <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> <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> <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> <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> <http://type.id>
= thing.type_id
> > WHERE type.type = 'junk';
> >
> > but better again would be, this, right?
> >
> > SELECT thing.id <http://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> <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>
> <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>
> <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>
> <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
<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.
