Where clause when using polymorphic_identity on base class

[Dmytro Starosud]

Base class A1 contains polymorphic_identity (along with polymorphic_on), but Query(A1)doesn't produce where clause, whereas Query(A2) (where A2 is subclass of A1 with its own polymorphic_identity) does.

Tried looking in docs with no success. I think I am just missing something.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Query, configure_mappers

Base = declarative_base()


class A1(Base):
    __tablename__ = 'a1'
    id = Column(Integer, primary_key=True)
    poly_on = Column(String, nullable=False)
    __mapper_args__ = {
        'polymorphic_on': poly_on,
        'polymorphic_identity': 'a1',
    }


class A2(A1):
    __mapper_args__ = {
        'polymorphic_identity': 'a2',
    }


configure_mappers()

print(Query(A1))
# SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on
# FROM a1

print(Query(A2))
# SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on
# FROM a1
# WHERE a1.poly_on IN (:poly_on_1)

I would expect WHERE clauses in both cases.

Originally posted here.

[Simon King]

From a quick test, this appears to be special behaviour for the base
class in an inheritance hierarchy. If you extend your test like this:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Query, configure_mappers

Base = declarative_base()

class A1(Base):
__tablename__ = 'a1'
id = Column(Integer, primary_key=True)
poly_on = Column(String, nullable=False)
__mapper_args__ = {
'polymorphic_on': poly_on,
'polymorphic_identity': 'a1',
}

class A2(A1):
__mapper_args__ = {
'polymorphic_identity': 'a2',
}

class A2a(A2):
__mapper_args__ = {
'polymorphic_identity': 'a2a',
}

class A2b(A2):
__mapper_args__ = {
'polymorphic_identity': 'a2b',
}

if __name__ == '__main__':
for cls in [A1, A2, A2a, A2b]:
print('#' * 10)
print(cls)
print(Query(cls))


...then the output looks like this:

##########
<class '__main__.A1'>

SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on

FROM a1
##########
<class '__main__.A2'>

SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on

FROM a1
WHERE a1.poly_on IN (:poly_on_1, :poly_on_2, :poly_on_3)
##########
<class '__main__.A2a'>

SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on

FROM a1

WHERE a1.poly_on IN (:poly_on_1)

##########
<class '__main__.A2b'>

SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on

FROM a1

WHERE a1.poly_on IN (:poly_on_1)

In other words, if you query for A1, you will get every row from the
table, even if the polymorphic identity doesn't match any subclasses.
This makes sense if you think of every row in the table as being some
kind of (possibly specialized) A1.

However, if you query for A2, you will only get rows that are
specifically A2, A2a or A2b.

If you don't want this behaviour, you could probably insert another
class as the parent of A1, with no polymorphic identity. At that
point, I think queries for A1 would include "WHERE poly_on IN ('a1',
'a2')".

Hope that helps,

Simon

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/941f0c3e-e541-4554-8b4a-570c28afec64%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Dmytro Starosud]

Thanks for your reply!

> you could probably insert another class as the parent of A1, with no polymorphic identity.

This is what I would like to avoid. Otherwise I would need to write some metaclass machinery to inject base class for all hundred models I have.

Originally I am going to add this __mapper_args__ via mixin with declared_attr.

пʼятниця, 21 червня 2019 р. 15:08:47 UTC+3 користувач Simon King написав:

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Simon King]

The extra base class is only needed once per inheritance hierarchy. Do
you have a hundred of those? Do you mind if I ask what kind of data
you are storing, and how you are structuring your class hierarchy?

Thanks,

Simon

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/37e43d97-94ec-4669-9147-4568545186a4%40googlegroups.com.

[Dmytro Starosud]

Thank you Simon.

I think you're right. I should better ask another question, instead of enrolling XY problem here (in short I need to add particular filter to each model).

But let's wrap up with this one. Did I understand correctly that I cannot have two following things simultaneously:

1. polymorphic_identity on base class;

2. automatic filter on polymorphic_on for base class;

Thanks,

Dmytro

 

пт, 21 черв. 2019 о 16:42 Simon King <si...@simonking.org.uk> пише:

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexegOMLGF8-h_yPD%2BAqGBGh1kC9Bjp475qAH9oa%3DdPQoMA%40mail.gmail.com.

[Mike Bayer]

what would the first WHERE clause be limiting on ?

an A2 is an A1, so if you are querying for all A1 objects, you should get those that are A2 as well.

if you want to affect how this works you can use the before_compile event to add whatever filters you'd like, see the example at  https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery

Originally posted here.

--

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+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

[Dmytro Starosud]

I see, thank you, Mike.

So, looks like I just wanted strange thing: having class to be distinct from itself.

Thank you for clarifying!

Dmytro

вт, 25 черв. 2019 о 19:26 Mike Bayer <mik...@zzzcomputing.com> пише:

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ce1721a8-0a31-497f-8969-c79b8f786541%40www.fastmail.com.