def test_correlated_lazyload(self):
class User(_base.ComparableEntity):
pass
class Stuff(_base.ComparableEntity):
pass
mapper(Stuff, stuff)
stuff_view = sa.select([
stuff.c.id
]).where(stuff.c.user_id == user_t.c.id).correlate(user_t).order_by(
sa.desc(stuff.c.date)).limit(1)
mapper(User,
user_t,
properties={
'stuff':
relation(Stuff,
primaryjoin=sa.and_(
user_t.c.id == stuff.c.user_id,
stuff.c.id == (stuff_view.as_scalar())))
})
sess = create_session()
eq_(
sess.query(User).all(), [
User(name='user1',
stuff=[Stuff(date=datetime.date(2007, 12, 15), id=2)]),
User(name='user2',
stuff=[Stuff(id=4, date=datetime.date(2008, 1, 15))]),
User(name='user3',
stuff=[Stuff(id=5, date=datetime.date(2007, 6, 15))])
])
def test_basic(self):
mapper(Employee, employees)
mapper(Department, departments, properties=dict(
employees=relation(Employee,
lazy=False,
backref='department')))
d1 = Department(name='One')
for e in 'Jim', 'Jack', 'John', 'Susan':
d1.employees.append(Employee(name=e))
d2 = Department(name='Two')
for e in 'Joe', 'Bob', 'Mary', 'Wally':
d2.employees.append(Employee(name=e))
sess = create_session()
sess.add_all((d1, d2))
sess.flush()
q = (sess.query(Department).
join('employees').
filter(Employee.name.startswith('J')).
distinct().
order_by([sa.desc(Department.name)]))
eq_(q.count(), 2)
assert q[0] is d2
def test_configured_order_by(self):
mapper(User, users, properties={
'addresses':dynamic_loader(mapper(Address, addresses), order_by=desc(Address.email_address))
})
sess = create_session()
u = sess.query(User).get(8)
eq_(list(u.addresses), [Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**')])
def test_basic(self):
mapper(Employee, employees)
mapper(Department, departments, properties=dict(employees=relation(Employee, lazy=False, backref="department")))
d1 = Department(name="One")
for e in "Jim", "Jack", "John", "Susan":
d1.employees.append(Employee(name=e))
d2 = Department(name="Two")
for e in "Joe", "Bob", "Mary", "Wally":
d2.employees.append(Employee(name=e))
sess = create_session()
sess.add_all((d1, d2))
sess.flush()
q = (
sess.query(Department)
.join("employees")
.filter(Employee.name.startswith("J"))
.distinct()
.order_by([sa.desc(Department.name)])
)
eq_(q.count(), 2)
assert q[0] is d2
def test_basic(self):
mapper(Employee, employees)
mapper(Department,
departments,
properties=dict(employees=relation(
Employee, lazy=False, backref='department')))
d1 = Department(name='One')
for e in 'Jim', 'Jack', 'John', 'Susan':
d1.employees.append(Employee(name=e))
d2 = Department(name='Two')
for e in 'Joe', 'Bob', 'Mary', 'Wally':
d2.employees.append(Employee(name=e))
sess = create_session()
sess.add_all((d1, d2))
sess.flush()
q = (sess.query(Department).join('employees').filter(
Employee.name.startswith('J')).distinct().order_by(
[sa.desc(Department.name)]))
eq_(q.count(), 2)
assert q[0] is d2
def test_nesting_with_functions(self):
mapper(Data, datas)
mapper(Foo, foo, properties={"data": relation(Data, backref=backref("foo", uselist=False))})
mapper(Stat, stats, properties={"data": relation(Data)})
session = create_session()
data = [Data(a=x) for x in range(5)]
session.add_all(data)
session.add_all(
(
Stat(data=data[0], somedata=1),
Stat(data=data[1], somedata=2),
Stat(data=data[2], somedata=3),
Stat(data=data[3], somedata=4),
Stat(data=data[4], somedata=5),
Stat(data=data[0], somedata=6),
Stat(data=data[1], somedata=7),
Stat(data=data[2], somedata=8),
Stat(data=data[3], somedata=9),
Stat(data=data[4], somedata=10),
)
)
session.flush()
arb_data = sa.select(
[stats.c.data_id, sa.func.max(stats.c.somedata).label("max")],
stats.c.data_id <= 5,
group_by=[stats.c.data_id],
).alias("arb")
arb_result = arb_data.execute().fetchall()
# order the result list descending based on 'max'
arb_result.sort(key=lambda a: a["max"], reverse=True)
# extract just the "data_id" from it
arb_result = [row["data_id"] for row in arb_result]
# now query for Data objects using that above select, adding the
# "order by max desc" separately
q = (
session.query(Data)
.options(sa.orm.eagerload("foo"))
.select_from(datas.join(arb_data, arb_data.c.data_id == datas.c.id))
.order_by(sa.desc(arb_data.c.max))
.limit(10)
)
# extract "data_id" from the list of result objects
verify_result = [d.id for d in q]
eq_(verify_result, arb_result)
def test_order_by(self):
mapper(User,
users,
properties={
'addresses': dynamic_loader(mapper(Address, addresses))
})
sess = create_session()
u = sess.query(User).get(8)
eq_(list(u.addresses.order_by(desc(Address.email_address))), [
Address(email_address=u'*****@*****.**'),
Address(email_address=u'*****@*****.**'),
Address(email_address=u'*****@*****.**')
])
def test_nesting_with_functions(self):
mapper(Data, datas)
mapper(Foo,
foo,
properties={
'data': relation(Data,
backref=backref('foo', uselist=False))
})
mapper(Stat, stats, properties={'data': relation(Data)})
session = create_session()
data = [Data(a=x) for x in range(5)]
session.add_all(data)
session.add_all(
(Stat(data=data[0], somedata=1), Stat(data=data[1], somedata=2),
Stat(data=data[2], somedata=3), Stat(data=data[3], somedata=4),
Stat(data=data[4], somedata=5), Stat(data=data[0], somedata=6),
Stat(data=data[1], somedata=7), Stat(data=data[2], somedata=8),
Stat(data=data[3], somedata=9), Stat(data=data[4], somedata=10)))
session.flush()
arb_data = sa.select(
[stats.c.data_id,
sa.func.max(stats.c.somedata).label('max')],
stats.c.data_id <= 5,
group_by=[stats.c.data_id]).alias('arb')
arb_result = arb_data.execute().fetchall()
# order the result list descending based on 'max'
arb_result.sort(key=lambda a: a['max'], reverse=True)
# extract just the "data_id" from it
arb_result = [row['data_id'] for row in arb_result]
# now query for Data objects using that above select, adding the
# "order by max desc" separately
q = (session.query(Data).options(sa.orm.eagerload('foo')).select_from(
datas.join(arb_data, arb_data.c.data_id == datas.c.id)).order_by(
sa.desc(arb_data.c.max)).limit(10))
# extract "data_id" from the list of result objects
verify_result = [d.id for d in q]
eq_(verify_result, arb_result)
def test_query(self):
q = Session.query(User).filter(User.name == 'ed').options(
eagerload(User.addresses))
eq_(q.all(), [
User(name='ed',
addresses=[Address(id=2),
Address(id=3),
Address(id=4)])
])
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
def go():
eq_(q2.all(), [
User(name='ed',
addresses=[Address(id=2),
Address(id=3),
Address(id=4)])
])
self.assert_sql_count(testing.db, go, 1)
eq_(
q2.join(User.addresses).filter(
Address.email == '*****@*****.**').value(func.count('*')), 1)
u1 = Session.query(User).get(8)
q = Session.query(Address).filter(Address.user == u1).order_by(
desc(Address.email))
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
eq_(q2.all(), [
Address(email='*****@*****.**'),
Address(email='*****@*****.**'),
Address(email='*****@*****.**')
])
q = Session.query(User).join(User.addresses).filter(
Address.email.like('%fred%'))
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
eq_(q2.all(), [User(name='fred')])
eq_(list(q2.values(User.id, User.name)), [(9, u'fred')])
def test_configured_order_by(self):
mapper(User, users, properties={
'addresses':dynamic_loader(mapper(Address, addresses), order_by=desc(Address.email_address))
})
sess = create_session()
u = sess.query(User).get(8)
eq_(list(u.addresses), [Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**')])
# test cancellation of None, replacement with something else
eq_(
list(u.addresses.order_by(None).order_by(Address.email_address)),
[Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**')]
)
# test cancellation of None, replacement with nothing
eq_(
set(u.addresses.order_by(None)),
set([Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**'), Address(email_address=u'*****@*****.**')])
)
def test_orderby_desc(self):
mapper(Address, addresses)
mapper(User, users, properties = dict(
addresses = relation(Address, lazy=True, order_by=[sa.desc(addresses.c.email_address)]),
))
sess = create_session()
assert [
User(id=7, addresses=[
Address(id=1)
]),
User(id=8, addresses=[
Address(id=2, email_address='*****@*****.**'),
Address(id=4, email_address='*****@*****.**'),
Address(id=3, email_address='*****@*****.**'),
]),
User(id=9, addresses=[
Address(id=5)
]),
User(id=10, addresses=[])
] == sess.query(User).all()
def test_orderby_desc(self):
mapper(Address, addresses)
mapper(User,
users,
properties=dict(addresses=relation(
Address,
lazy=True,
order_by=[sa.desc(addresses.c.email_address)]), ))
sess = create_session()
assert [
User(id=7, addresses=[Address(id=1)]),
User(id=8,
addresses=[
Address(id=2, email_address='*****@*****.**'),
Address(id=4, email_address='*****@*****.**'),
Address(id=3, email_address='*****@*****.**'),
]),
User(id=9, addresses=[Address(id=5)]),
User(id=10, addresses=[])
] == sess.query(User).all()
def test_query(self):
q = Session.query(User).filter(User.name=='ed').options(eagerload(User.addresses))
eq_(q.all(), [User(name='ed', addresses=[Address(id=2), Address(id=3), Address(id=4)])])
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
def go():
eq_(q2.all(), [User(name='ed', addresses=[Address(id=2), Address(id=3), Address(id=4)])])
self.assert_sql_count(testing.db, go, 1)
eq_(q2.join(User.addresses).filter(Address.email=='*****@*****.**').value(func.count('*')), 1)
u1 = Session.query(User).get(8)
q = Session.query(Address).filter(Address.user==u1).order_by(desc(Address.email))
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
eq_(q2.all(), [Address(email='*****@*****.**'), Address(email='*****@*****.**'), Address(email='*****@*****.**')])
q = Session.query(User).join(User.addresses).filter(Address.email.like('%fred%'))
q2 = serializer.loads(serializer.dumps(q), users.metadata, Session)
eq_(q2.all(), [User(name='fred')])
eq_(list(q2.values(User.id, User.name)), [(9, u'fred')])
def test_order_by(self):
query = create_session().query(Foo)
assert query.order_by([Foo.bar])[0].bar == 0
assert query.order_by([sa.desc(Foo.bar)])[0].bar == 99
def test_order_by(self):
query = create_session().query(Foo)
assert query.order_by([Foo.bar])[0].bar == 0
assert query.order_by([sa.desc(Foo.bar)])[0].bar == 99
def test_selectby(self):
res = create_session().query(Foo).filter_by(range=5)
assert res.order_by([Foo.bar])[0].bar == 5
assert res.order_by([sa.desc(Foo.bar)])[0].bar == 95
def test_selectby(self):
res = create_session().query(Foo).filter_by(range=5)
assert res.order_by([Foo.bar])[0].bar == 5
assert res.order_by([sa.desc(Foo.bar)])[0].bar == 95
def test_correlated_lazyload(self):
class User(_base.ComparableEntity):
pass
class Stuff(_base.ComparableEntity):
pass
mapper(Stuff, stuff)
stuff_view = sa.select([stuff.c.id]).where(stuff.c.user_id==user_t.c.id).correlate(user_t).order_by(sa.desc(stuff.c.date)).limit(1)
mapper(User, user_t, properties={
'stuff':relation(Stuff, primaryjoin=sa.and_(user_t.c.id==stuff.c.user_id, stuff.c.id==(stuff_view.as_scalar())))
})
sess = create_session()
eq_(sess.query(User).all(), [
User(name='user1', stuff=[Stuff(date=datetime.date(2007, 12, 15), id=2)]),
User(name='user2', stuff=[Stuff(id=4, date=datetime.date(2008, 1 , 15))]),
User(name='user3', stuff=[Stuff(id=5, date=datetime.date(2007, 6, 15))])
])
