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.
