On Oct 19, 2013, at 4:24 PM, Sebastian Elsner <[email protected]> wrote:
> Hello, > > using the Address and User example, where the Address is connected to > the User via a many-to-many relationship, I want to get all users with > the date of their newest address. This is what I have now: > > s.query(User, s.query(func.max(Address.created)).\ > filter(Address.users.any()).correlate(User).as_scalar()).\ > outerjoin(User.addresses).all() > > But this is giving me all users with the newest address in the whole > address table. I think the error is in the subquery's filter, but I fail > to see how I can fix it. I am also not tied to this query, so if you > know a better way to get a list of all Users and their newest address > date, shoot! the format for this is the "select user rows + an aggregate of a related table", this format is illustrated here: http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-subqueries where we illustrate the count of address rows per user. I see here though you have an association table in between them so that just has to be added to the subquery to create a row that goes across Address and UserAddresses, same idea though, use subquery with aggregate + group_by, (outer) join to that: subq = session.query( func.max(Address.created).label("created"), UserAddresses.user_id).join(UserAddresses).\ group_by(UserAddresses.user_id).subquery() q = session.query(User, subq.c.created).outerjoin(subq) print q.all() > > Here is a working example. As you can see if you run it, even Users with > no Addresses assigned will get the newest address date in the query. > > import datetime > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.engine import create_engine > from sqlalchemy.orm.session import sessionmaker > from sqlalchemy.schema import Column, ForeignKey > from sqlalchemy.types import Integer, DateTime, String > from sqlalchemy.orm import relationship > from sqlalchemy.sql.expression import func > > Base = declarative_base() > > > class Address(Base): > __tablename__ = 'address' > id = Column(Integer, primary_key=True) > created = Column(DateTime) > users = relationship('User', back_populates='addresses', > secondary='useraddress') > > def __repr__(self): > return "Address: %s, %s" % (self.id, self.created) > > > class User(Base): > __tablename__ = 'user' > id = Column(Integer, primary_key=True) > name = Column(String) > addresses = relationship('Address', back_populates='users', > secondary='useraddress') > > def __repr__(self): > return "User: " + self.name > > > class UserAddresses(Base): > __tablename__ = 'useraddress' > user_id = Column(Integer, ForeignKey('user.id'), primary_key=True) > address_id = Column(Integer, ForeignKey('address.id'), primary_key=True) > > engine = create_engine('sqlite://') > Base.metadata.create_all(engine) > session = sessionmaker(engine)() > > u1 = User(name="Foo") > u2 = User(name="Bar") > u1.addresses.append(Address(created=datetime.datetime.now())) > u1.addresses.append(Address(created=datetime.datetime.now() - > datetime.timedelta(days=1))) > session.add(u1) > session.add(u2) > session.commit() > print u1, u1.addresses > print u2, u2.addresses > print session.query(User, print session.query(User, > session.query(func.max(Address.created)).filter(Address.users.any()).correlate(User).as_scalar()).outerjoin(User.addresses).all() > > Cheers > > Sebastian > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
