you can use multiple types, but the question is if you want to *load*
multiple subtypes *at the same time* while not loading others    if
you need to load sub-groups of subtypes all at once then
AbstractConcreteBase is going to create lots of problems for you and
it probably can't handle this use case very cleanly if at all, unless
you are fine having it base off the core UNION statement that
AbstractConcreteBase generates (e.g. the polymorphic_union).    Any
time the ORM is asked to load OrderItem objects, it's going to go back
to that original UNION statement and there's no way for it to not be
there - it will get in the way any time you want the query to be less
than that single huge UNION.  You can filter on subtypes by adding a
WHERE clause to the outside but internally you'd still be scanning the
tables for every subtype.

it would be much more feasible if you could state your mappings in
terms of a different structure, for example, if every subtype features
a foreign key to a common table, make that table your base class and
use joined table inheritance.

I would need to see more specifically what kinds of tables you're
dealing with to make a better recommendation.




> On Wed, Mar 7, 2018 at 6:01 PM, Harshvardhan Gupta <harshsay...@gmail.com> 
> wrote:
> I dont plan to use just one type.
>
> Your example queries worked for me, but I have a few more doubts.
>
> How can I :
>
> Perform filter on a particular sub type ? E.g. I want the OrderTestItem to
> have only those that are newer than x date
> Load just few sub types ? If i had 10 different items, how could I load just
> 3 of them ?
> How can I perform load_only on the sub types' fields ?
>
>
> On Wednesday, 7 March 2018 17:55:51 UTC-5, Mike Bayer wrote:
>>
>> if you only need to load against one sub-type at a time, then you
>> shouldn't use any polymorphic features or concrete inheritance.  Make
>> your OrderItem just a non-mapped mixin class, then OrderItemTest and
>> whatever else are just normal mapped-to-one-table classes.
>>
>> On Wed, Mar 7, 2018 at 5:39 PM, Mike Bayer <mik...@zzzcomputing.com>
>> wrote:
>> > On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta
>> > <harsh...@gmail.com> wrote:
>> >> Calling configure_mappers() fixes the  issue.
>> >>
>> >> However, the next thing I tried was to do joined load ( for eager
>> >> loading )
>> >> , and it fails.
>> >>
>> >> This is what I tried :
>> >>
>> >>
>> >> order =
>> >>
>> >> db.session.query(Order).join(Order.items.of_type(order_items)).options(joinedload(Order.items.of_type(order_items))).all()
>> >
>> > OK, first off, all the instructions I gave you are wrong.  the whole
>> > generation of "pjoin" here is wrong because AbstractConcreteBase has
>> > already generated that for you, it is already mapped to the OrderItem
>> > class and that's what it's for.  So you would not create a
>> > with_polymorpic() object of your own and especially not with the alias
>> > name "pjoin" because that is conflicting.
>> >
>> > Your example works pretty much as you probably are looking for just like
>> > this:
>> >
>> > order = s.query(Order).options(joinedload(Order.items)).all()[0]
>> >
>> > the "pjoin" is already there.
>> >
>> > it also can work with the join() if you use contains_eager() (you
>> > normally don't mix join() and joinedload() at the same time, they are
>> > conflicting), though you have to get at the pjoin that's been
>> > generated for you:
>> >
>> > order = s.query(Order).join(Order.items).options(
>> >     contains_eager(Order.items,
>> >                    alias=OrderItem.__mapper__.selectable)).all()[0]
>> >
>> >
>> > I've created
>> > https://bitbucket.org/zzzeek/sqlalchemy/issues/4213/joined-eager-load-unaliased-column-check
>> > is added to see if i can figure out some potential bugs here but the
>> > concrete polymorphic use case is not well supported.   I don't think
>> > of_type() is really worked out to know what it's doing when it is
>> > given a polymorphic_union and the base class already has a
>> > polymorphic_union mapped.   joined loading certainly doesn't.
>> >
>> > Concrete polymorphic mapping with relationships is really really
>> > really hard.  I wrote this stuff, and apparently I don't even know how
>> > to use it.    You're better off sticking with joined/ single table
>> > inheritance if you need polymorphic loading and eager loads and you
>> > are trying to get something done, rather than working on SQLAlchemy
>> > bugs.
>> >
>> >
>> >
>> >>
>> >>
>> >>
>> >>
>> >> This returns an error:
>> >>
>> >> InvalidRequestError: Detected unaliased columns when generating joined
>> >> load.
>> >> Make sure to use aliased=True or flat=True when using joined loading
>> >> with
>> >> with_polymorphic().
>> >>
>> >>
>> >>
>> >> So I added aliased= True in my polymorphic union, and when i query, i
>> >> the
>> >> same error:
>> >>
>> >>
>> >> InvalidRequestError: Detected unaliased columns when generating joined
>> >> load.
>> >> Make sure to use aliased=True or flat=True when using joined loading
>> >> with
>> >> with_polymorphic().
>> >>
>> >>
>> >>
>> >>
>> >> On Wednesday, 7 March 2018 16:05:49 UTC-5, Mike Bayer wrote:
>> >>>
>> >>> my test doesn't have that problem, try calling configure_mappers()
>> >>> first.   Try running the test case I have as given.
>> >>>
>> >>> On Wed, Mar 7, 2018 at 4:02 PM, Harshvardhan Gupta
>> >>> <harsh...@gmail.com> wrote:
>> >>> > Thanks for the reply.
>> >>> >
>> >>> > Sorry, I will be more careful when i sent code snippets.
>> >>> >
>> >>> > I tried this out after you asked me to remove flat =True, so the
>> >>> > code
>> >>> > looks
>> >>> > like this now :
>> >>> >
>> >>> >
>> >>> > pjoin = polymorphic_union({
>> >>> >     'order_test_item': OrderTestItem.__table__,
>> >>> > }, 'type', 'pjoin')   # I dont think 'type' should exist.
>> >>> >
>> >>> >
>> >>> > order_items = with_polymorphic(
>> >>> >     OrderItem, [OrderTestItem], selectable=pjoin)
>> >>> >
>> >>> >
>> >>> > Now i get a different Error :
>> >>> >
>> >>> >
>> >>> >
>> >>> > ---------------------------------------------------------------------------
>> >>> > UnmappedClassError                        Traceback (most recent
>> >>> > call
>> >>> > last)
>> >>> > <ipython-input-10-d281fb038587> in <module>()
>> >>> >       4
>> >>> >       5 order_items = with_polymorphic(
>> >>> > ----> 6     OrderItem, [OrderTestItem], selectable=pjoin)
>> >>> >
>> >>> >
>> >>> >
>> >>> > ~/.virtualenvs/app-flask-rest-backend-36/lib/python3.6/site-packages/sqlalchemy/orm/util.py
>> >>> > in with_polymorphic(base, classes, selectable, flat, polymorphic_on,
>> >>> > aliased, innerjoin, _use_mapper_path, _existing_alias)
>> >>> >     771        only be specified if querying for one specific
>> >>> > subtype
>> >>> > only
>> >>> >     772     """
>> >>> > --> 773     primary_mapper = _class_to_mapper(base)
>> >>> >     774     if _existing_alias:
>> >>> >     775         assert _existing_alias.mapper is primary_mapper
>> >>> >
>> >>> >
>> >>> >
>> >>> > ~/.virtualenvs/app-flask-rest-backend-36/lib/python3.6/site-packages/sqlalchemy/orm/base.py
>> >>> > in _class_to_mapper(class_or_mapper)
>> >>> >     308         return insp.mapper
>> >>> >     309     else:
>> >>> > --> 310         raise exc.UnmappedClassError(class_or_mapper)
>> >>> >     311
>> >>> >     312
>> >>> >
>> >>> > UnmappedClassError: Class 'models.Item.OrderItem' is not mapped
>> >>> >
>> >>> >
>> >>> > My models are defined as my original post, like this :
>> >>> >
>> >>> >
>> >>> > class OrderItem(Dictifiable, AbstractConcreteBase, db.Model):
>> >>> >  pass
>> >>> >
>> >>> >
>> >>> > class OrderTestItem(OrderItem):
>> >>> >  order_id = Column(Integer, ForeignKey("order.id"),
>> >>> > primary_key=True)
>> >>> >  test_id = Column(Integer, ForeignKey("test.id"), primary_key=True)
>> >>> >
>> >>> >  test = relationship('Test')
>> >>> >  order = relationship('Order')
>> >>> >
>> >>> >  __mapper_args__ = {
>> >>> >  'polymorphic_identity': 'order_test_item',
>> >>> >  'concrete': True,
>> >>> >  }
>> >>> >
>> >>> >
>> >>> > class Order(Dictifiable, db.Model):
>> >>> >     id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
>> >>> >     rp_id = Column(Integer, nullable=False)
>> >>> >
>> >>> >     user_id = Column(Integer, ForeignKey('user.id'))
>> >>> >
>> >>> >     user = relationship('User')
>> >>> >     items = relationship('OrderItem')
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> > On Wednesday, 7 March 2018 15:48:58 UTC-5, Mike Bayer wrote:
>> >>> >>
>> >>> >> here's a full MCVE, which with "flat=True" reproduces your issue
>> >>> >> exactly (send me this next time).   The issue is the "flat=True",
>> >>> >> take
>> >>> >> that out because it isn't compatible with a concrete union - it
>> >>> >> implies the "aliased" flag and that gets in the way of what the
>> >>> >> polymorphic_union() function is doing already.
>> >>> >>
>> >>> >>
>> >>> >> from sqlalchemy import *
>> >>> >> from sqlalchemy.orm import *
>> >>> >> from sqlalchemy.ext.declarative import declarative_base
>> >>> >> from sqlalchemy.ext.declarative import declared_attr
>> >>> >> from sqlalchemy.ext.declarative import AbstractConcreteBase
>> >>> >>
>> >>> >> Base = declarative_base()
>> >>> >>
>> >>> >>
>> >>> >> class OrderItem(AbstractConcreteBase, Base):
>> >>> >>     pass
>> >>> >>
>> >>> >>
>> >>> >> class OrderTestItem(OrderItem):
>> >>> >>     __tablename__ = 'order_test_item'
>> >>> >>
>> >>> >>     order_id = Column(Integer, ForeignKey("order.id"),
>> >>> >> primary_key=True)
>> >>> >>     test_id = Column(Integer, ForeignKey("test.id"),
>> >>> >> primary_key=True)
>> >>> >>
>> >>> >>     test = relationship('Test')
>> >>> >>     order = relationship('Order')
>> >>> >>
>> >>> >>     __mapper_args__ = {
>> >>> >>         'polymorphic_identity': 'order_test_item',
>> >>> >>         'concrete': True
>> >>> >>     }
>> >>> >>
>> >>> >>
>> >>> >> class User(Base):
>> >>> >>     __tablename__ = 'user'
>> >>> >>     id = Column(Integer, primary_key=True)
>> >>> >>
>> >>> >>
>> >>> >> class Test(Base):
>> >>> >>     __tablename__ = 'test'
>> >>> >>     id = Column(Integer, primary_key=True)
>> >>> >>
>> >>> >>
>> >>> >> class Order(Base):
>> >>> >>     __tablename__ = 'order'
>> >>> >>
>> >>> >>     id = Column(Integer, primary_key=True)
>> >>> >>
>> >>> >>     user_id = Column(Integer, ForeignKey('user.id'))
>> >>> >>
>> >>> >>     user = relationship('User')
>> >>> >>     items = relationship('OrderItem')
>> >>> >>
>> >>> >> e = create_engine("sqlite://", echo=True)
>> >>> >> Base.metadata.create_all(e)
>> >>> >>
>> >>> >> s = Session(e)
>> >>> >>
>> >>> >> t1, t2 = Test(), Test()
>> >>> >> s.add(Order(items=[OrderTestItem(test=t1),
>> >>> >> OrderTestItem(test=t2)]))
>> >>> >> s.commit()
>> >>> >>
>> >>> >>
>> >>> >> pjoin = polymorphic_union({
>> >>> >>     'order_test_item': OrderTestItem.__table__,
>> >>> >> }, 'type', 'pjoin')
>> >>> >>
>> >>> >> order_items = with_polymorphic(
>> >>> >>     OrderItem, [OrderTestItem], selectable=pjoin)
>> >>> >>
>> >>> >>
>> >>> >> s = Session(e)
>> >>> >> order = s.query(Order).join(Order.items.of_type(order_items)).all()
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >> On Wed, Mar 7, 2018 at 3:36 PM, Harshvardhan Gupta
>> >>> >> <harsh...@gmail.com> wrote:
>> >>> >> > Thanks for replying.
>> >>> >> > I forgot to show the error.
>> >>> >> > It is this:
>> >>> >> >
>> >>> >> > 1054, "Unknown column 'pjoin.order_id' in 'on clause'") [SQL:
>> >>> >> > "SELECT
>> >>> >> > `order`.id AS order_id, `order`.rp_id AS order_rp_id,
>> >>> >> > `order`.user_id
>> >>> >> > AS
>> >>> >> > order_user_id \nFROM `order` INNER JOIN (SELECT
>> >>> >> > order_test_item.order_id
>> >>> >> > AS
>> >>> >> > order_id, order_test_item.test_id AS test_id, 'order_test_item'
>> >>> >> > AS
>> >>> >> > type
>> >>> >> > \nFROM order_test_item) AS anon_1 ON `order`.id = pjoin.order_id"
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> > On Wednesday, 7 March 2018 15:28:54 UTC-5, Mike Bayer wrote:
>> >>> >> >>
>> >>> >> >> On Tue, Mar 6, 2018 at 8:36 PM, Harshvardhan Gupta
>> >>> >> >> <harsh...@gmail.com> wrote:
>> >>> >> >> > I tried to use AbstractConcreteBase for polymorphic
>> >>> >> >> > relationships
>> >>> >> >> > ,
>> >>> >> >> > but
>> >>> >> >> > I am
>> >>> >> >> > getting errors. The examples in sqlalchemy cover normal
>> >>> >> >> > polymorphism
>> >>> >> >> > well,
>> >>> >> >> > but not those with Abstract Base classes.
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > I have already asked a question on stack overflow .
>> >>> >> >> >
>> >>> >> >> > The gist of the question is:
>> >>> >> >> >
>> >>> >> >> > class OrderItem(Dictifiable, AbstractConcreteBase, db.Model):
>> >>> >> >> >     pass
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > class OrderTestItem(OrderItem):
>> >>> >> >> >     order_id = Column(Integer, ForeignKey("order.id"),
>> >>> >> >> > primary_key=True)
>> >>> >> >> >     test_id = Column(Integer, ForeignKey("test.id"),
>> >>> >> >> > primary_key=True)
>> >>> >> >> >
>> >>> >> >> >     test = relationship('Test')
>> >>> >> >> >     order = relationship('Order')
>> >>> >> >> >
>> >>> >> >> >     __mapper_args__ = {
>> >>> >> >> >         'polymorphic_identity': 'order_test_item',
>> >>> >> >> >         'concrete': True
>> >>> >> >> >     }
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > class Order(Dictifiable, db.Model): # This class has a
>> >>> >> >> > relation to
>> >>> >> >> > the
>> >>> >> >> > polymorphic class
>> >>> >> >> >
>> >>> >> >> >     id = Column(Integer, Sequence('user_id_seq'),
>> >>> >> >> > primary_key=True)
>> >>> >> >> >
>> >>> >> >> >     user_id = Column(Integer, ForeignKey('user.id'))
>> >>> >> >> >
>> >>> >> >> >     user = relationship('User')
>> >>> >> >> >     items = relationship('OrderItem')
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > I query like :
>> >>> >> >> >
>> >>> >> >> > pjoin = polymorphic_union({
>> >>> >> >> >     'order_test_item': OrderTestItem.__table__,
>> >>> >> >> > }, 'type', 'pjoin')
>> >>> >> >> >
>> >>> >> >> > order_items =
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > with_polymorphic(OrderItem,[OrderTestItem],selectable=pjoin,flat=True)
>> >>> >> >> >
>> >>> >> >> > And my actual query :
>> >>> >> >> >
>> >>> >> >> > order =
>> >>> >> >> > Order.query.join(Order.items.of_type(order_items)).all()
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > I would like to know what the correct way to query these
>> >>> >> >> > tables
>> >>> >> >> > is,
>> >>> >> >> > how
>> >>> >> >> > to
>> >>> >> >> > eager load polymorphic tables, and how to filter on the
>> >>> >> >> > relationships.
>> >>> >> >>
>> >>> >> >> looks correct to me.  what is "the error" ?   can you provide
>> >>> >> >> full
>> >>> >> >> MCVE + complete stack trace?
>> >>> >> >>
>> >>> >> >>
>> >>> >> >>
>> >>> >> >> >
>> >>> >> >> > I plan to send a pull request with an example of these test
>> >>> >> >> > cases
>> >>> >> >> > after
>> >>> >> >> > I
>> >>> >> >> > know the answers myself.
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > --
>> >>> >> >> > 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.
>> >>> >> >> > 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+...@googlegroups.com.
>> >>> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> >>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> >> To post to this group, send email to sqlal...@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.

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

Reply via email to