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!
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.
