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.