def test_unwrap_as_scalar(self):
customer = self.tables.customer
order = self.tables.order
sub_stmt = nested(select([order]).as_scalar()).where(order.c.customer_id
== customer.c.id).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
self._assert_basic(stmt)
def test_render_core_nested_alone(self):
Customer = self.classes.Customer
Order = self.classes.Order
n = nested(Order).where(Customer.orders)
self.assert_compile(
n, '(SELECT "order".id, "order".customer_id, "order".order_info '
'FROM "order", customer WHERE customer.id = "order".customer_id)')
def test_implicit_select_scalar_arg(self):
customer = self.tables.customer
order = self.tables.order
sub_stmt = nested(order).where(order.c.customer_id
== customer.c.id).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
self._assert_basic(stmt)
def test_double(self):
customer = self.tables.customer
order = self.tables.order
item = self.tables.item
sub_sub_stmt = nested(select([item]).where(item.c.order_id ==
order.c.id)).label('i')
sub_stmt = nested(select([sub_sub_stmt]).where(order.c.customer_id ==
customer.c.id)).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
self.assert_compile(
stmt,
'SELECT (SELECT (SELECT item.id, item.order_id, item.price, '
'item.quantity FROM item WHERE item.order_id = "order".id) AS i '
'FROM "order" WHERE "order".customer_id = customer.id) AS o '
'FROM customer WHERE customer.id = %(id_1)s'
)
def test_render_core_nested_alone(self):
Customer = self.classes.Customer
Order = self.classes.Order
n = nested(Order).where(Customer.orders)
self.assert_compile(
n,
'(SELECT "order".id, "order".customer_id, "order".order_info '
'FROM "order", customer WHERE customer.id = "order".customer_id)'
)
def test_double_nested_row(self):
customer = self.tables.customer
order = self.tables.order
item = self.tables.item
sub_sub_stmt = nested(
select([item]).where(item.c.order_id == order.c.id)).label('i')
sub_stmt = nested(
select([sub_sub_stmt
]).where(order.c.customer_id == customer.c.id)).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
r = config.db.execute(stmt)
row = r.fetchone()
sub_result = row['o']
sub_sub_result = sub_result.fetchone()['i']
eq_(list(sub_sub_result), [(1001, 101, Decimal('9.99'), 1),
(1002, 101, Decimal('19.99'), 2)])
sub_sub_result = sub_result.fetchone()['i']
eq_(list(sub_sub_result), [(1003, 102, Decimal('9.99'), 1)])
def test_render_core_basic_nested(self):
Customer = self.classes.Customer
Order = self.classes.Order
s = Session()
n = nested(Order).where(Customer.orders)
q = s.query(Customer, n)
self.assert_compile(
q, 'SELECT customer.id AS customer_id, '
'customer.name AS customer_name, (SELECT "order".id, '
'"order".customer_id, "order".order_info FROM "order" '
'WHERE customer.id = "order".customer_id) AS anon_1 '
'FROM customer')
def test_str_via_default_compiler(self):
customer = self.tables.customer
order = self.tables.order
sub_stmt = nested(select([order]).where(order.c.customer_id
== customer.c.id)).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
eq_(
str(stmt).replace("\n", ""),
'SELECT (SELECT "order".id, "order".customer_id, "order".order_info '
'FROM "order" WHERE "order".customer_id = customer.id) AS o '
'FROM customer WHERE customer.id = :id_1'
)
def test_render_core_basic_nested(self):
Customer = self.classes.Customer
Order = self.classes.Order
s = Session()
n = nested(Order).where(Customer.orders)
q = s.query(Customer, n)
self.assert_compile(
q,
'SELECT customer.id AS customer_id, '
'customer.name AS customer_name, (SELECT "order".id, '
'"order".customer_id, "order".order_info FROM "order" '
'WHERE customer.id = "order".customer_id) AS anon_1 '
'FROM customer'
)
def test_nested_type_trans(self):
customer = self.tables.customer
order = self.tables.order
item = self.tables.item
class SpecialType(TypeDecorator):
impl = Integer
def process_result_value(self, value, dialect):
return str(value) + "_processed"
sub_sub_stmt = nested(select([type_coerce(item.c.price, SpecialType)]).\
where(item.c.order_id ==
order.c.id)).label('i')
sub_stmt = nested(
select([sub_sub_stmt
]).where(order.c.customer_id == customer.c.id)).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
r = config.db.execute(stmt)
row = r.fetchone()
sub_result = row['o']
sub_sub_result = sub_result.fetchone()['i']
eq_(list(sub_sub_result), [('9.99_processed', ),
('19.99_processed', )])
def test_nested_row(self):
customer = self.tables.customer
order = self.tables.order
sub_stmt = nested(
select([order
]).where(order.c.customer_id == customer.c.id)).label('o')
stmt = select([sub_stmt]).where(customer.c.id == 1)
r = config.db.execute(stmt)
row = r.fetchone()
sub_result = row['o']
eq_(list(sub_result), [(101, 1, 'apple related'),
(102, 1, 'apple related'),
(103, 1, 'apple related')])
def test_load_collection_raw(self):
Customer = self.classes.Customer
Order = self.classes.Order
s = Session()
n = nested(Order).where(Customer.orders)
q = s.query(Customer, n).filter(Customer.id == 1)
with self.assert_statement_count(1):
row = q.all()[0]
eq_(row[0], self._orm_fixture()[0])
eq_(row[1].fetchall(), [(101, 1, u'apple related'),
(102, 1, u'apple related'),
(103, 1, u'apple related')])
def test_load_collection_raw(self):
Customer = self.classes.Customer
Order = self.classes.Order
s = Session()
n = nested(Order).where(Customer.orders)
q = s.query(Customer, n).filter(Customer.id == 1)
with self.assert_statement_count(1):
row = q.all()[0]
eq_(row[0], self._orm_fixture()[0])
eq_(row[1].fetchall(), [
(101, 1, u'apple related'), (102, 1, u'apple related'),
(103, 1, u'apple related')
])
