This is just anazing! I was tinkering with the select statement, but using the correlate_except would never have come to my mind. Thank you!
Am 22.10.2013 00:16, schrieb Michael Bayer: > if we're talking about just the timestamp, then that would be a column > property and if you don't want it to load normally it would be under a > deferred(). > > An analogue of the subquery example using count() is here: > http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#using-column-property > > again, you can adapt this to look like your max() + association table: > > > class User(Base): > __tablename__ = 'user' > id = Column(Integer, primary_key=True) > > address_created = column_property( > select([func.max(Address.created)]).\ > where(Address.id == UserAddresses.address_id).\ > where(UserAddresses.user_id == id).\ > correlate_except(Address, UserAddresses) > ) > > On Oct 21, 2013, at 3:12 AM, Sebastian Elsner <sebast...@risefx.com> wrote: > >> Thank you for the help! Additionally, I was wondering if it would be >> able to make "newest_address" an attribute on the user class, which can >> then be used in a query with ".options(joinedload('newest_address'))". >> My goal would be that I get an attribute that returns the newest' >> address date time on normal access and is also able to be eagerly >> loaded/joined in a query object. I read up in the docs and either >> column_property or Correlated Subquery Relatonship Hybrid seems to be >> made for this. Which one should I use? >> >> >> >> ButAm 20.10.2013 04:41, schrieb Michael Bayer: >>> On Oct 19, 2013, at 4:24 PM, Sebastian Elsner <sebast...@risefx.com> 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 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/groups/opt_out. >> -- >> 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/groups/opt_out. -- 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/groups/opt_out.
