Ejemplo n.º 1
0
    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)
Ejemplo n.º 2
0
 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)
Ejemplo n.º 3
0
    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)
Ejemplo n.º 4
0
    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)
Ejemplo n.º 5
0
    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)
Ejemplo n.º 6
0
 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)
Ejemplo n.º 7
0
 def test_alias(self) -> None:
     table_alias = alias('users', 'u')
     self.assertSql('users AS u', table_alias)
     self.assertParams((), table_alias)