Re: [sqlalchemy] join & aliased
OK, thank you
On Saturday, March 22, 2014 9:03:01 PM UTC+1, Michael Bayer wrote:
>
>
> On Mar 22, 2014, at 9:16 AM, lars van gemerden
> >
> wrote:
>
> > query = session.query(Email)
> > query = query.join("user") #or query = query.join("user",
> aliased = True)
> > query = query.add_columns(Email.email, User.name)
>
> the add_columns() method does not have the clause adaptation behavior of
> filter(), so it does not take into account the fact that “aliased=True” was
> called when the User entity was first pulled in. so this pulls in the User
> entity twice, once from User.name, and another because of the aliased(User)
> brought in by the join.
>
> solution is not to use aliased=True (a feature I would never have added if
> it were today), use ua = aliased(User); q.join(ua, “user”);
> query.add_columns(ua.name).
>
>
>
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] join & aliased
On Mar 22, 2014, at 9:16 AM, lars van gemerden wrote:
> query = session.query(Email)
> query = query.join("user") #or query = query.join("user", aliased =
> True)
> query = query.add_columns(Email.email, User.name)
the add_columns() method does not have the clause adaptation behavior of
filter(), so it does not take into account the fact that "aliased=True" was
called when the User entity was first pulled in. so this pulls in the User
entity twice, once from User.name, and another because of the aliased(User)
brought in by the join.
solution is not to use aliased=True (a feature I would never have added if it
were today), use ua = aliased(User); q.join(ua, "user");
query.add_columns(ua.name).
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] join & aliased
Hi all,
Maybe (hopefully;-) i am missing something simple, but i have the following
test code:
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
class User(Base):
__tablename__ = 'user_table'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship("Email", back_populates="user")
class Email(Base):
__tablename__ = 'email_table'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user_table.id'))
user = relationship("User", back_populates="addresses")
Base.metadata.create_all(engine)
u1 = User(name = "bob")
u2 = User(name = "jan")
a1 = Email(email = "b...@school.nl", user = u1)
a2 = Email(email = "j...@school.nl", user = u2)
a3 = Email(email = "j...@home.nl", user = u2)
session = Session()
session.add_all([u1, u2, a1, a2, a3])
session.commit()
query = session.query(Email)
query = query.join("user") #or query = query.join("user", aliased =
True)
query = query.add_columns(Email.email, User.name)
for r in query.all():
print r[1:],
print
and if i use the line:
query = query.join("user")
this results in:
(u'b...@school.nl', u'bob') (u'j...@school.nl', u'jan')
(u'j...@home.nl', u'jan')
which i would expect, but if i exchange the line with:
query = query.join("user", aliased = True)
i get the full cross product between the tables:
(u'b...@school.nl', u'bob') (u'j...@school.nl', u'bob') (u'j...@home.nl',
u'bob') (u'b...@school.nl', u'jan') (u'j...@school.nl', u'jan')
(u'j...@home.nl', u'jan')
Can anyone explain why this difference occurs?
Cheers, Lars
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
