I have one example illustrating that A.b.id does not pick up the correct 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 <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.
