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 <mike...@zzzcomputing.com> wrote: > On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta > <harshsay...@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+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.
