Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

just to add, getting full flexibility in abstract concrete loading is
possibly technically feasible, I have some ideas here, but would
require a lot of effort on my part.  i have ideas how to get what you
want but it's nothing trivial or that I have any near-term
availability to work on.   But I'm not happy how badly the
abstract base behaves in conjunction with the newer "polymorphic"
features.

On Wed, Mar 7, 2018 at 6:26 PM, Mike Bayer  wrote:
> you can use multiple types, but the question is if you want to *load*
> multiple subtypes *at the same time* while not loading othersif
> 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  
>> 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 
>>> wrote:
>>> > On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta
>>> >  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 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

you can use multiple types, but the question is if you want to *load*
multiple subtypes *at the same time* while not loading othersif
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  
> 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 
>> wrote:
>> > On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta
>> >  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 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

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  wrote:
> On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta
>  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
>>>  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 :
>>> >
>>> >
>>> > ---
>>> > UnmappedClassErrorTraceback (most recent call
>>> > last)
>>> >  in ()
>>> >   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)
>>> > 771only 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)
>>> > 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

On Wed, Mar 7, 2018 at 4:19 PM, Harshvardhan Gupta
 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
>>  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 :
>> >
>> >
>> > ---
>> > UnmappedClassErrorTraceback (most recent call
>> > last)
>> >  in ()
>> >   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)
>> > 771only 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 = 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Harshvardhan Gupta]

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() 




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 
>  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 : 
> > 
> > 
> --- 
> > UnmappedClassErrorTraceback (most recent call 
> last) 
> >  in () 
> >   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) 
> > 771only 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' 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

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
 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 :
>
> ---
> UnmappedClassErrorTraceback (most recent call last)
>  in ()
>   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)
> 771only 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
>>  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

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Harshvardhan Gupta]

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) in ()  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)771only be specified 
if querying for one specific subtype only772 """--> 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.mapper309  
   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 
>  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 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

found a few mentions of "flat=True" that fail to specify that this
flag should **not** be used when selectable= is set, pushing extra
verbiage to that effect now.   most likely with_polymorphic() should
raise an error if both flags are set.

On Wed, Mar 7, 2018 at 3:48 PM, 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
>  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
>>>  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 

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

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

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Harshvardhan Gupta]

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

Re: [sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Mike Bayer]

On Tue, Mar 6, 2018 at 8:36 PM, Harshvardhan Gupta
 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+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.

[sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Harshvardhan Gupta]

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. 

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