def test_same_lvl_groupby(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=2, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
result = self.session.query(
CustomersModel.id, func.count(CustomersModel.id)).join(
OrdersModel,
CustomersModel.id == OrdersModel.customer_id).group_by(
CustomersModel.id).all()
self.assertEqual(result, [(1, 2), (2, 3)])
ACL.unset_user()
def test_high_lvl_join(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
client = CustomersModel(id=3, name='James Bond', phone_number='007')
self.session.add(client)
self.session.commit()
result = self.session.query(CustomersModel, OrdersModel).join(
OrdersModel, CustomersModel.id == OrdersModel.customer_id).all()
self.assertEqual(result, [(client, orders[4])])
ACL.unset_user()
def test_low_lvl_join(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3,
order_date='08-28-1998') # dla zmyły
]
self.session.add_all(orders)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
join = [(customers[0], orders[0]), (customers[0], orders[1]),
(customers[1], orders[2]), (customers[1], orders[3])]
result = self.session.query(CustomersModel, OrdersModel).join(
OrdersModel, CustomersModel.id == OrdersModel.customer_id).all()
self.assertEqual(result, join)
ACL.unset_user()
def test_same_lvl_aggr(self):
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
self.assertEqual(
self.session.query(func.count(OrdersModel.id)).scalar(), 5)
ACL.unset_user()
def test_high_lvl_having(self):
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=5, customer_id=2, order_date='09-15-1998'),
OrdersModel(id=6, customer_id=2, order_date='10-15-1998'),
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
query = self.session.query(OrdersModel.customer_id, func.count(OrdersModel.customer_id)).\
group_by(OrdersModel.customer_id).having(func.count(OrdersModel.customer_id) > 2).all()
self.assertEqual(query, [])
ACL.unset_user()
def test_low_lvl_subquery(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=5, customer_id=1, order_date='10-31-1998'),
OrdersModel(id=6, customer_id=2, order_date='10-15-1998'),
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
subquery = self.session.query(
OrdersModel.customer_id,
func.max(OrdersModel.order_date).label('latest_order')).group_by(
OrdersModel.customer_id).subquery()
query = self.session.query(
CustomersModel.name, subquery.c.latest_order).join(
subquery, CustomersModel.id == subquery.c.customer_id).all()
self.assertEqual(query, [('Will Smith', '10-31-1998'),
('Tom Hanks', '10-15-1998')])
ACL.unset_user()
def test_join2(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
orders = [
OrdersModel(id=1, customer_id=1, order_date='2021-01-01'),
OrdersModel(id=2, customer_id=2, order_date='2021-01-01'),
OrdersModel(id=3, customer_id=1, order_date='2021-01-01')
]
articles = [
ArticlesModel(id=1, order_id=1, box_id=1, quantity=10),
ArticlesModel(id=2, order_id=1, box_id=4, quantity=5),
ArticlesModel(id=3, order_id=2, box_id=2, quantity=20),
ArticlesModel(id=4, order_id=3, box_id=2, quantity=12),
]
self.session.add_all(customers)
self.session.commit()
self.session.add_all(orders)
self.session.commit()
self.session.add_all(articles)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=3, name='Mel Gibson', phone_number='444-555-666')
]
orders = [
OrdersModel(id=4, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=5, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=6, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=7, customer_id=2, order_date='2021-01-01')
]
articles = [
ArticlesModel(id=5, order_id=4, box_id=1, quantity=10),
ArticlesModel(id=6, order_id=5, box_id=4, quantity=5),
ArticlesModel(id=7, order_id=6, box_id=2, quantity=15),
ArticlesModel(id=8, order_id=7, box_id=2, quantity=100),
]
self.session.add_all(customers)
self.session.commit()
self.session.add_all(orders)
self.session.commit()
self.session.add_all(articles)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
result = self.session.query(CustomersModel.name, func.avg(ArticlesModel.quantity)).\
join(OrdersModel, CustomersModel.id == OrdersModel.customer_id).\
join(ArticlesModel, OrdersModel.id == ArticlesModel.order_id).\
group_by(CustomersModel.id).all()
self.assertEqual(result, [('Will Smith', 9), ('Tom Hanks', 20)])
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
result = self.session.query(CustomersModel.name, func.avg(ArticlesModel.quantity)).\
join(OrdersModel, CustomersModel.id == OrdersModel.customer_id).\
join(ArticlesModel, OrdersModel.id == ArticlesModel.order_id).\
group_by(CustomersModel.id).all()
self.assertEqual(result, [('Will Smith', 9), ('Tom Hanks', 60),
('Mel Gibson', 10)])
ACL.unset_user()