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 
> <javascript:>> wrote: 
> > On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta 
> > <harsh...@gmail.com <javascript:>> 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 <javascript:>. 
> >> To post to this group, send email to sqlal...@googlegroups.com 
> <javascript:>. 
> >> 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