Thank you for your quick answer Mike.
Le mardi 26 avril 2016 00:28:10 UTC+2, Mike Bayer a écrit :
>
>
>
> well this usage above is wrong. You can't have contains_eager() and
> joinedload() along the same paths at the same time like that. Also,
> chaining joinedload() from contains_eager() is not a very typical thing
> to do, it works, but joinedload() does not coordinate with
> contains_eager() in any way, and it has no idea that you are using
> outerjoin() to the left of it.
>
> The two correct ways to do this are:
>
> print session.query(Plant).\
> join(Plant.plant_dimensionsseries).\
>
> options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
>
>
> ))
>
I cannot use a inner JOIN between plant and plant_dimensionsseries because
a plant does not necessarily have any corresponding plant_dimensionsseries
so give me different result.
>
> and
>
> print session.query(Plant).\
> outerjoin(Plant.plant_dimensionsseries).\
>
> options(contains_eager(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed,
>
>
> innerjoin=False))
>
>
> or of course:
>
>
> print session.query(Plant).\
> outerjoin(Plant.plant_dimensionsseries).\
>
> options(joinedload(Plant.plant_dimensionsseries).joinedload(PlantDimensionsseries.data_computed))
>
>
>
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 -*-
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.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'])
)
class Plant(Base):
__table__ = _plant_table
class Dimensionsseriestype(Base):
__table__ = _dimensionsseriestype_table
_id = __table__.c.id
class PlantDimensionsseries(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'))
class PlantDimensionsseriesDataComputed(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)
print session.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)
--
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.
