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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
