On 04/26/2016 08:09 AM, Rob Fowler wrote:
I have a complete example here:
https://gist.github.com/mianos/42cf15928f27cc9dfde9996d2e593e78
Ideas? I am sure it's possible. At the moment I am just using a "orderby
desc" on the relationship and using [0] to get the first.
this example helps a lot for me to just fill in how to make it work.
The relationship system isn't sophisticated enough to locate the
"User.id" column buried within a correlated subquery in order to
manipulate it for lazyloads or eager loads. So we have to organize the
primaryjoin so that "User.id" is not inside a subquery. Here's a way to
do that:
class User(Base):
__tablename__ = 'tbl_users'
id = Column(types.Integer, primary_key=True)
mobile = relationship(
"Mobile",
primaryjoin=(
"and_("
"User.id == Mobile.user_id,"
"Mobile.id.in_(select([func.max(Mobile.id)]).group_by(Mobile.user_id))"
")"
),
uselist=False
)
class Mobile(Base):
__tablename__ = 'tbl_mobiles'
id = Column(types.Integer, primary_key=True)
user_id = Column(types.Integer, ForeignKey(User.id,
ondelete="CASCADE"), nullable=False)
the other way to go here is to use "relationship to non primary mapper"
as detailed at
http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#relationship-to-non-primary-mapper,
which in fact will get you a more optimized query for this, but is more
cumbersome to use on the Python side:
stmt = select([func.max(Mobile.id).label('id'), Mobile.user_id]).\
group_by(Mobile.user_id).alias()
user_mobile = mapper(
Mobile, stmt, primary_key=[stmt.c.id],
non_primary=True
)
User.mobile = relationship(
user_mobile,
primaryjoin=User.id == stmt.c.user_id,
viewonly=True,
uselist=False
)
for u, m in session.query(User, user_mobile) \
.join(User.mobile) \
.filter(User.id == 1082):
print u.id, m.id
for u in session.query(User).filter(User.id == 1082):
print u.mobile.id
I've attached my example scripts for both as a reference.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import create_engine, types, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref, remote, foreign
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select, func, desc
from sqlalchemy.orm import mapper
from sqlalchemy.orm import aliased
connection = "sqlite://"
Base = declarative_base()
class User(Base):
__tablename__ = 'tbl_users'
id = Column(types.Integer, primary_key=True)
mobile = relationship(
"Mobile",
primaryjoin=(
"and_("
"User.id == Mobile.user_id,"
"Mobile.id.in_(select([func.max(Mobile.id)]).group_by(Mobile.user_id))"
")"
),
uselist=False
)
class Mobile(Base):
__tablename__ = 'tbl_mobiles'
id = Column(types.Integer, primary_key=True)
user_id = Column(types.Integer, ForeignKey(User.id, ondelete="CASCADE"), nullable=False)
if __name__ == '__main__':
engine = create_engine(connection, echo='debug')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
User(id=1082),
User(id=1083),
Mobile(id=5, user_id=1082),
Mobile(id=6, user_id=1082),
Mobile(id=7, user_id=1082),
Mobile(id=8, user_id=1083),
Mobile(id=9, user_id=1083),
])
for u, m in session.query(User, Mobile) \
.join(User.mobile) \
.filter(User.id == 1082):
print u.id, m.id
for u in session.query(User).filter(User.id == 1082):
print u.mobile.id
m1 = session.query(Mobile).get(7)
assert m1 is u.mobile
from sqlalchemy import create_engine, types, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref, remote, foreign
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select, func, desc
from sqlalchemy.orm import mapper
from sqlalchemy.orm import aliased
connection = "sqlite://"
Base = declarative_base()
class User(Base):
__tablename__ = 'tbl_users'
id = Column(types.Integer, primary_key=True)
class Mobile(Base):
__tablename__ = 'tbl_mobiles'
id = Column(types.Integer, primary_key=True)
user_id = Column(types.Integer, ForeignKey(User.id, ondelete="CASCADE"), nullable=False)
stmt = select([func.max(Mobile.id).label('id'), Mobile.user_id]).\
group_by(Mobile.user_id).alias()
user_mobile = mapper(
Mobile, stmt, primary_key=[stmt.c.id],
non_primary=True
)
User.mobile = relationship(
user_mobile,
primaryjoin=User.id == stmt.c.user_id,
viewonly=True,
uselist=False
)
if __name__ == '__main__':
engine = create_engine(connection, echo='debug')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
User(id=1082),
User(id=1083),
Mobile(id=5, user_id=1082),
Mobile(id=6, user_id=1082),
Mobile(id=7, user_id=1082),
Mobile(id=8, user_id=1083),
Mobile(id=9, user_id=1083),
])
for u, m in session.query(User, user_mobile) \
.join(User.mobile) \
.filter(User.id == 1082):
print u.id, m.id
for u in session.query(User).filter(User.id == 1082):
print u.mobile.id
m1 = session.query(Mobile).get(7)
assert m1 is u.mobile
