On Fri, Dec 28, 2018 at 9:21 AM Mehdi Gmira <mgm...@wiremind.fr> wrote:
>
> I have one example illustrating that A.b.id does not pick up the correct id
okey doke, set that all up explicitly as below, will update the
example now, thanks for the complete test
B_viacd = mapper(B, j, non_primary=True, primary_key=[j.c.b_id], properties={
"id": j.c.b_id,
"c_id": j.c.c_id,
"d_c_id": j.c.d_c_id,
"b_id": [j.c.b_id, j.c.d_b_id],
"d_id": j.c.d_id,
})
>
> from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func,
> Table, create_engine, join
> from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload,
> contains_eager, mapper
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
> engine = create_engine('sqlite://', echo=True)
>
>
> class A(Base):
> __tablename__ = 'a'
>
> id = Column(Integer, primary_key=True)
> b_id = Column(ForeignKey('b.id'))
>
> class B(Base):
> __tablename__ = 'b'
>
> id = Column(Integer, primary_key=True)
>
> class C(Base):
> __tablename__ = 'c'
>
> id = Column(Integer, primary_key=True)
> a_id = Column(ForeignKey('a.id'))
>
> class D(Base):
> __tablename__ = 'd'
>
> id = Column(Integer, primary_key=True)
> c_id = Column(ForeignKey('c.id'))
> b_id = Column(ForeignKey('b.id'))
>
> # 1. set up the join() as a variable, so we can refer
> # to it in the mapping multiple times.
> j = join(B, D, D.b_id == B.id).join(C, C.id == D.c_id)
>
>
> # 2. Create a new mapper() to B, with non_primary=True.
> # Columns in the join with the same name must be
> # disambiguated within the mapping, using named properties.
> B_viacd = mapper(B, j, non_primary=True, properties={
> "b_id": [j.c.b_id, j.c.d_b_id],
> "d_id": j.c.d_id
> })
>
> A.b = relationship(B_viacd, primaryjoin=A.b_id == B_viacd.c.b_id)
>
>
> Base.metadata.drop_all(engine)
> Base.metadata.create_all(engine)
> Session = sessionmaker(engine)
> session = Session()
>
>
> # we insert one B object
> b = B()
> session.add(b)
> session.flush()
> assert b.id == 1
>
> # we insert one A object
> a = A()
> a.b_id = b.id
> session.add(a)
> session.flush()
>
> # we insert two C objects
> c = C()
> session.add(c)
> session.flush()
>
> c2 = C()
> c2.a_id = a.id
> session.add(c2)
> session.flush()
>
> d = D()
> d.b_id = b.id
> d.c_id = c2.id
> session.add(d)
> session.flush()
>
> queried_a = session.query(A).one()
> assert queried_a.b.id == 1 # fails because b.id = 2 (while it should be = 1)
> because we have one B object
>
>
> Le ven. 28 déc. 2018 à 15:13, Mike Bayer <mike...@zzzcomputing.com> a écrit :
>>
>> On Fri, Dec 28, 2018 at 4:24 AM Mehdi Gmira <mgm...@wiremind.fr> wrote:
>> >
>> > Thanks for the reply. There is something I don't understand in the example:
>> >
>> > B_viacd = mapper(B, j, non_primary=True, properties={
>> > "b_id": [j.c.b_id, j.c.d_b_id],
>> > "d_id": j.c.d_id
>> > })
>> >
>> >
>> > How Is the mapper supposed to map the results of j with the class B ? the
>> > statement behind j is:
>> >
>> >> SELECT d.id AS d_id, b.id AS b_id, d.b_id AS d_b_id, d.c_id AS d_c_id,
>> >> c.id AS c_id, c.a_id AS c_a_id
>> >> FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id
>> >
>> >
>> > d.id is mapped to d_id, and b.id to b_id. So I don't understand how the
>> > ORM can map b.id to B.id.
>>
>> I think the answer is that they are not, only columns that match up
>> with what's established in the primary mapper will be apparent on the
>> returned objects. Id have to check to see exactly what happens.
>> But you should be getting the columns that were originally mapped.
>> if not, then we need to open an issue with a complete MCVE.
>>
>>
>> >
>> > Le ven. 28 déc. 2018 à 00:25, Mike Bayer <mike...@zzzcomputing.com> a
>> > écrit :
>> >>
>> >> the section at
>> >> https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
>> >> has a recipe for: "when we seek to join from A to B, making use of any
>> >> number of C, D, etc. in between, however there are also join
>> >> conditions between A and B directly", I think that's this case because
>> >> you have conditions between Game/YearStadium directly. So I think use
>> >> that pattern.
>> >>
>> >>
>> >>
>> >> On Thu, Dec 27, 2018 at 6:18 AM <mgm...@wiremind.fr> wrote:
>> >> >
>> >> > from sqlalchemy import Column, DateTime, String, Integer, ForeignKey,
>> >> > func, Table, create_engine
>> >> > from sqlalchemy.orm import relationship, backref, sessionmaker,
>> >> > joinedload, contains_eager
>> >> > from sqlalchemy.ext.declarative import declarative_base
>> >> >
>> >> > Base = declarative_base()
>> >> > engine = create_engine('sqlite://', echo=True)
>> >> >
>> >> >
>> >> > class Game(Base):
>> >> > __tablename__ = 'game'
>> >> >
>> >> > id = Column(Integer, primary_key=True)
>> >> >
>> >> > stadium_name = Column(String)
>> >> > month_id = Column(Integer, ForeignKey('month.id'))
>> >> >
>> >> > year_stadium = relationship("YearStadium", secondary="month",
>> >> > primaryjoin="Month.id == Game.month_id",
>> >> > secondaryjoin="and_(Month.year_name == YearStadium.year_name, "
>> >> > "YearStadium.stadium_name == Game.stadium_name)")
>> >> >
>> >> >
>> >> > class Month(Base):
>> >> > __tablename__ = 'month'
>> >> >
>> >> > id = Column(Integer, primary_key=True)
>> >> >
>> >> > year_name = Column(String)
>> >> >
>> >> >
>> >> > class YearStadium(Base):
>> >> > __tablename__ = 'year_stadium'
>> >> >
>> >> > id = Column(Integer, primary_key=True)
>> >> >
>> >> > stadium_name = Column(String)
>> >> > year_name = Column(String)
>> >> >
>> >> >
>> >> > Base.metadata.drop_all(engine)
>> >> > Base.metadata.create_all(engine)
>> >> > Session = sessionmaker(engine)
>> >> > session = Session()
>> >> >
>> >> >
>> >> > session.query(Game).join(Game.year_stadium).all() # works
>> >> > session.query(Game).options(joinedload(Game.year_stadium)) # fails
>> >> >
>> >> > In the example above, I'm trying to have a relationship to a table.
>> >> > It's not a classic association table example, since the third table
>> >> > needs one column from table 2 and one column from table 1 to be joined.
>> >> > The relationship I wrote works well for simple joins or when
>> >> > lazy-loading, but fails with a joinedload.
>> >> >
>> >> > Is there a way to make it work ?
>> >> >
>> >> > --
>> >> > 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.
>> >
>> >
>> >
>> > --
>> > Mehdi GMIRA
>> > Lead Developer - RMPulse
>> > Mail: mgm...@wiremind.fr
>> > Mobile: +33 6 99 31 56 69
>> > Wiremind SAS
>> >
>> > --
>> > 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.
>
>
>
> --
> Mehdi GMIRA
> Lead Developer - RMPulse
> Mail: mgm...@wiremind.fr
> Mobile: +33 6 99 31 56 69
> Wiremind SAS
>
> --
> 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.
