def test_orderby_desc(self):
Address, addresses, users, User = (
self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User,
)
mapper(Address, addresses)
mapper(
User,
users,
properties=dict(addresses=relationship(
Address,
lazy="select",
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_basic(self):
Department, Employee, employees, departments = (
self.classes.Department,
self.classes.Employee,
self.tables.employees,
self.tables.departments,
)
mapper(Employee, employees)
mapper(
Department,
departments,
properties=dict(employees=relationship(
Employee, lazy="joined", 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_order_by_func_label_desc(self):
stmt = select([func.foo("bar").label("fb"),
table1]).order_by(desc("fb"))
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS fb, mytable.myid, mytable.name, "
"mytable.description FROM mytable ORDER BY fb DESC",
)
def test_order_by(self):
User, Address = self._user_address_fixture()
sess = create_session()
u = sess.query(User).get(8)
eq_(
list(u.addresses.order_by(desc(Address.email_address))),
[
Address(email_address="*****@*****.**"),
Address(email_address="*****@*****.**"),
Address(email_address="*****@*****.**"),
],
)
def test_execute(self):
with testing.db.connect() as conn:
conn.execute(cattable.insert().values(id=9, description="Python"))
cats = conn.execute(cattable.select().order_by(cattable.c.id))
eq_([(9, "Python")], list(cats))
result = conn.execute(cattable.insert().values(description="PHP"))
eq_([10], result.inserted_primary_key)
lastcat = conn.execute(
cattable.select().order_by(desc(cattable.c.id))
)
eq_((10, "PHP"), lastcat.first())
def test_reflect(self):
t1.insert().execute({u("méil"): 2, ue("\u6e2c\u8a66"): 7})
t2.insert().execute({u("a"): 2, u("b"): 2})
t3.insert().execute({
ue("\u6e2c\u8a66_id"): 2,
ue("unitable1_\u6e2c\u8a66"): 7,
u("Unitéble2_b"): 2,
ue("\u6e2c\u8a66_self"): 2,
})
meta = MetaData(testing.db)
tt1 = Table(t1.name, meta, autoload=True)
tt2 = Table(t2.name, meta, autoload=True)
tt3 = Table(t3.name, meta, autoload=True)
tt1.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 5})
tt2.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 1})
tt3.insert().execute({
ue("\u6e2c\u8a66_id"): 1,
ue("unitable1_\u6e2c\u8a66"): 5,
u("Unitéble2_b"): 1,
ue("\u6e2c\u8a66_self"): 1,
})
self.assert_(
tt1.select(
order_by=desc(u("méil"))).execute().fetchall() == [(2,
7), (1,
5)])
self.assert_(
tt2.select(
order_by=desc(u("méil"))).execute().fetchall() == [(2,
2), (1,
1)])
self.assert_(
tt3.select(order_by=desc(ue(
"\u6e2c\u8a66_id"))).execute().fetchall() == [(2, 7, 2,
2), (1, 5, 1,
1)])
def test_correlated_lazyload(self):
stuff, user_t = self.tables.stuff, self.tables.user_t
class User(fixtures.ComparableEntity):
pass
class Stuff(fixtures.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":
relationship(
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_query_one(self):
q = (
Session.query(User)
.filter(User.name == "ed")
.options(joinedload(User.addresses))
)
q2 = serializer.loads(serializer.dumps(q, -1), 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(literal_column("*"))),
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, -1), users.metadata, Session)
eq_(
q2.all(),
[
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
],
)
def test_stateful(self):
class MyThingy(ColumnClause):
def __init__(self):
super(MyThingy, self).__init__("MYTHINGY!")
@compiles(MyThingy)
def visit_thingy(thingy, compiler, **kw):
if not hasattr(compiler, "counter"):
compiler.counter = 0
compiler.counter += 1
return str(compiler.counter)
self.assert_compile(
select([column("foo"), MyThingy()]).order_by(desc(MyThingy())),
"SELECT foo, 1 ORDER BY 2 DESC",
)
self.assert_compile(
select([MyThingy(), MyThingy()]).where(MyThingy() == 5),
"SELECT 1, 2 WHERE 3 = :MYTHINGY!_1",
)
def test_executemany(self):
with testing.db.connect() as conn:
conn.execute(
cattable.insert(),
[
{"id": 89, "description": "Python"},
{"id": 8, "description": "Ruby"},
{"id": 3, "description": "Perl"},
{"id": 1, "description": "Java"},
],
)
cats = conn.execute(cattable.select().order_by(cattable.c.id))
eq_(
[(1, "Java"), (3, "Perl"), (8, "Ruby"), (89, "Python")],
list(cats),
)
conn.execute(
cattable.insert(),
[{"description": "PHP"}, {"description": "Smalltalk"}],
)
lastcats = conn.execute(
cattable.select().order_by(desc(cattable.c.id)).limit(2)
)
eq_([(91, "Smalltalk"), (90, "PHP")], list(lastcats))
def test_standalone_units_stringable(self):
self.assert_compile(desc("somelabel"), "somelabel DESC")
def test_selectby(self):
Foo = self.classes.Foo
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_order_by(self):
Foo = self.classes.Foo
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_nesting_with_functions(self):
Stat, Foo, stats, foo, Data, datas = (
self.classes.Stat,
self.classes.Foo,
self.tables.stats,
self.tables.foo,
self.classes.Data,
self.tables.datas,
)
mapper(Data, datas)
mapper(
Foo,
foo,
properties={
"data": relationship(Data,
backref=backref("foo", uselist=False))
},
)
mapper(Stat, stats, properties={"data": relationship(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],
)
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]
arb_data = arb_data.alias("arb")
# now query for Data objects using that above select, adding the
# "order by max desc" separately
q = (session.query(Data).options(sa.orm.joinedload("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)