def test_join_full(self) -> None:
select = self._factory \
.select('u.username', 'c.country') \
.from_(alias('users', 'u')) \
.full_join(alias('countries', 'c'), on('u.country_id', 'c.id'))
self.assertSql(
' '.join([
'SELECT u.username, c.country',
'FROM users AS u',
'FULL JOIN countries AS c ON u.country_id = c.id',
]),
select
)
self.assertParams((), select)
def test_join(self) -> None:
select = self._factory \
.select('u.username', 'r.role', 'c.country') \
.from_(alias('users', 'u')) \
.join(alias('roles', 'r'), on('u.role_id', 'r.id')) \
.join(alias('countries', 'c'), on('u.country_id', 'c.id'))
self.assertSql(
' '.join([
'SELECT u.username, r.role, c.country',
'FROM users AS u',
'JOIN roles AS r ON u.role_id = r.id',
'JOIN countries AS c ON u.country_id = c.id'
]),
select
)
self.assertParams((), select)
def test_order_by_direction(self) -> None:
select = self._factory \
.select(
'u.id',
'u.username',
alias(func('COUNT', 'l.id'), 'total')
) \
.from_(alias('users', 'u')) \
.join(alias('logins', 'l'), on('u.id', 'l.user_id')) \
.group_by('l.user_id') \
.order_by('u.username') \
.order_by('total', 'desc')
self.assertSql(
' '.join([
'SELECT u.id, u.username, COUNT(l.id) AS total',
'FROM users AS u',
'JOIN logins AS l ON u.id = l.user_id',
'GROUP BY l.user_id',
'ORDER BY u.username, total DESC',
]),
select
)
self.assertParams((), select)
def test_group_by(self) -> None:
select = self._factory \
.select(
alias(func('COUNT', 'id'), 'total')
) \
.from_('employees') \
.group_by('department')
self.assertSql(
' '.join([
'SELECT COUNT(id) AS total',
'FROM employees',
'GROUP BY department'
]),
select
)
self.assertParams((), select)
def test_having(self) -> None:
salary_sum = func('SUM', 'salary')
select = self._factory \
.select(
'department',
alias(salary_sum, 'total')
) \
.from_('employees') \
.group_by('department') \
.having(field(salary_sum).gt(5000))
self.assertSql(
' '.join([
'SELECT department, SUM(salary) AS total',
'FROM employees',
'GROUP BY department',
'HAVING SUM(salary) > ?'
]),
select
)
self.assertParams((5000, ), select)
def test_qualified_alias(self) -> None:
field_alias = alias('u.id', 'user_id')
self.assertSql('u.id AS user_id', field_alias)
self.assertParams((), field_alias)
def test_alias(self) -> None:
table_alias = alias('users', 'u')
self.assertSql('users AS u', table_alias)
self.assertParams((), table_alias)