Re: [sqlalchemy] newest address for each user
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.
Re: [sqlalchemy] newest address for each user
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
Re: [sqlalchemy] newest address for each user
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
[sqlalchemy] newest address for each user
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 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.
Re: [sqlalchemy] newest address for each user
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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
