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_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))]) ])