def test_noorm(self):
"""test the control case"""
# I want to display a list of tests owned by owner 1
# if someoption is false or he hasn't specified it yet (null)
# but not if he set it to true (example someoption is for hiding)
# desired output for owner 1
# test_id, cat_name
# 1 'Some Category'
# 3 "
# not orm style correct query
print "Obtaining correct results without orm"
result = (
sa.select(
[tests.c.id, categories.c.name],
sa.and_(tests.c.owner_id == 1, sa.or_(options.c.someoption == None, options.c.someoption == False)),
order_by=[tests.c.id],
from_obj=[
tests.join(categories).outerjoin(
options, sa.and_(tests.c.id == options.c.test_id, tests.c.owner_id == options.c.owner_id)
)
],
)
.execute()
.fetchall()
)
eq_(result, [(1, u"Some Category"), (3, u"Some Category")])
def test_outer_join(self):
"""Query.outerjoin"""
session = create_session()
q = (session.query(User).outerjoin(['orders', 'addresses']).filter(
sa.or_(Order.id == None, Address.id == 1)))
eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_noorm(self):
"""test the control case"""
# I want to display a list of tests owned by owner 1
# if someoption is false or he hasn't specified it yet (null)
# but not if he set it to true (example someoption is for hiding)
# desired output for owner 1
# test_id, cat_name
# 1 'Some Category'
# 3 "
# not orm style correct query
print "Obtaining correct results without orm"
result = sa.select([tests.c.id, categories.c.name],
sa.and_(
tests.c.owner_id == 1,
sa.or_(options.c.someoption == None,
options.c.someoption == False)),
order_by=[tests.c.id],
from_obj=[
tests.join(categories).outerjoin(
options,
sa.and_(
tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id))
]).execute().fetchall()
eq_(result, [(1, u'Some Category'), (3, u'Some Category')])
def test_outer_join_count(self):
"""test the join and outerjoin functions on Query"""
session = create_session()
q = (session.query(User).outerjoin(['orders', 'addresses']).
filter(sa.or_(Order.id == None, Address.id == 1)))
eq_(q.count(), 4)
def test_outer_join(self):
"""Query.outerjoin"""
session = create_session()
q = (session.query(User).outerjoin(['orders', 'addresses']).
filter(sa.or_(Order.id == None, Address.id == 1)))
eq_(set([User(id=7), User(id=8), User(id=10)]),
set(q.all()))
def test_from(self):
session = create_session()
sel = users.outerjoin(orders).outerjoin(
addresses, orders.c.address_id == addresses.c.id)
q = (session.query(User).select_from(sel).filter(
sa.or_(Order.id == None, Address.id == 1)))
eq_(set([User(id=7), User(id=8), User(id=10)]), set(q.all()))
def test_outer_join_count(self):
"""test the join and outerjoin functions on Query"""
session = create_session()
q = (session.query(User).outerjoin(['orders', 'addresses']).filter(
sa.or_(Order.id == None, Address.id == 1)))
eq_(q.count(), 4)
def test_from(self):
session = create_session()
sel = users.outerjoin(orders).outerjoin(
addresses, orders.c.address_id == addresses.c.id)
q = (session.query(User).select_from(sel).
filter(sa.or_(Order.id == None, Address.id == 1)))
eq_(set([User(id=7), User(id=8), User(id=10)]),
set(q.all()))
def test_dslish(self):
"""test the same as witheagerload except using generative"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload("category"))
l = q.filter(
sa.and_(tests.c.owner_id == 1, sa.or_(options.c.someoption == None, options.c.someoption == False))
).outerjoin("owner_option")
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u"1 Some Category", u"3 Some Category"])
def test_dslish(self):
"""test the same as witheagerload except using generative"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload('category'))
l = q.filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(
options.c.someoption == None,
options.c.someoption == False))).outerjoin('owner_option')
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def test_withouteagerload(self):
s = create_session()
l = (s.query(Test).
select_from(tests.outerjoin(options,
sa.and_(tests.c.id == options.c.test_id,
tests.c.owner_id ==
options.c.owner_id))).
filter(sa.and_(tests.c.owner_id==1,
sa.or_(options.c.someoption==None,
options.c.someoption==False))))
result = ["%d %s" % ( t.id,t.category.name ) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def test_withouteagerload(self):
s = create_session()
l = (s.query(Test).select_from(
tests.outerjoin(
options,
sa.and_(tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id))).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(options.c.someoption == None,
options.c.someoption == False))))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])
def test_witheagerload(self):
"""
Test that an eagerload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload("category"))
l = q.select_from(
tests.outerjoin(options, sa.and_(tests.c.id == options.c.test_id, tests.c.owner_id == options.c.owner_id))
).filter(sa.and_(tests.c.owner_id == 1, sa.or_(options.c.someoption == None, options.c.someoption == False)))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u"1 Some Category", u"3 Some Category"])
def test_witheagerload(self):
"""
Test that an eagerload locates the correct "from" clause with which to
attach to, when presented with a query that already has a complicated
from clause.
"""
s = create_session()
q = s.query(Test).options(sa.orm.eagerload('category'))
l = (q.select_from(
tests.outerjoin(
options,
sa.and_(tests.c.id == options.c.test_id,
tests.c.owner_id == options.c.owner_id))).filter(
sa.and_(
tests.c.owner_id == 1,
sa.or_(options.c.someoption == None,
options.c.someoption == False))))
result = ["%d %s" % (t.id, t.category.name) for t in l]
eq_(result, [u'1 Some Category', u'3 Some Category'])