On 04/26/2016 12:22 PM, Alex Dev wrote:
These two work. However I think that I cannot do that in my real query
because it is a bit more complex. For the sake of simplifying my
question, I reduced the query to the minimum but I realize it is now
hard so see why I was using contains_eager or joinedload in different
parts of the query. Indeed, I need to do more joins in the real query
(see below).
I can have the expected result by adding the |innerjoin=False| but you
wrote that this is a wrong usage so I wonder what a correct usage would
be in my real case.
|
# -*- coding: utf-8 -*-
fromsqlalchemy import*
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm import*
Base=declarative_base()
_plant_table =Table('plant',Base.metadata,
Column('id',Integer,primary_key=True)
)
_dimensionsseriestype_table =Table('dimensionsseriestype',Base.metadata,
Column('id',Integer,primary_key=True),
Column('sortindex',Integer),
Column('designation',String),
)
_plant_dimensionsseries_table =Table('plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(['plant_id'],['plant.id']),
ForeignKeyConstraint(['dimensionsseriestype_id'],['dimensionsseriestype.id']),
)
_view_plant_dimensionsseries_table
=Table('view_plant_dimensionsseries',Base.metadata,
Column('plant_id',Integer,primary_key=True),
Column('dimensionsseriestype_id',Integer,primary_key=True),
ForeignKeyConstraint(
['plant_id','dimensionsseriestype_id'],
['plant_dimensionsseries.plant_id','plant_dimensionsseries.dimensionsseriestype_id'])
)
classPlant(Base):
__table__ =_plant_table
classDimensionsseriestype(Base):
__table__ =_dimensionsseriestype_table
_id =__table__.c.id
classPlantDimensionsseries(Base):
__table__ =_plant_dimensionsseries_table
_plant_id =__table__.c.plant_id
_dimensionsseriestype_id =__table__.c.dimensionsseriestype_id
plant =relationship('Plant',
innerjoin=True,
backref=backref('plant_dimensionsseries'))
dimensionsseriestype =relationship('Dimensionsseriestype',
innerjoin=True,
backref=backref('plant_dimensionsseries'))
classPlantDimensionsseriesDataComputed(Base):
__table__ =_view_plant_dimensionsseries_table
_plant_id =__table__.c.plant_id
_dimensionsseriestype_id =__table__.c.dimensionsseriestype_id
# One-to-one relationship
dimensionsseries =relationship('PlantDimensionsseries',
innerjoin=True,
backref=backref('data_computed',
innerjoin=True))
if__name__ =='__main__':
engine
=create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
Session=sessionmaker(bind=engine)
session =Session()
# real query (almost)
printsession.query(Plant).\
outerjoin(Plant.plant_dimensionsseries).\
outerjoin(Dimensionsseriestype,PlantDimensionsseries.dimensionsseriestype).\
options(contains_eager(Plant.plant_dimensionsseries,PlantDimensionsseries.dimensionsseriestype)).\
options(joinedload(Plant.plant_dimensionsseries,PlantDimensionsseries.data_computed)).\
order_by(Dimensionsseriestype.sortindex)
if you're looking to joinedload() just
PlantDimentionssseries.data_computed and have the contains_eager() take
effect for the Plant.plant_dimensionseries part, and the issue is that
you want this to be two separate sets of options, you can do this:
options(contains_eager(Plant.plant_dimensionsseries)).
options(defaultload(Plant.plant_dimensionseries).joinedload(PlantDimensionsSeries.data_computed,
innerjoin=False))
if you need to use contains_eager() for one segment and joinedload() for
the second, then you need to give it innerjoin=False, this won't be
auto-detected.
|
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.