def test_selecting_with_aggregation(self):
sq = SelectQuery(
Blog, 't1.*, COUNT(t2.pk) AS count').group_by('id').join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
[]))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
[]))
sq = SelectQuery(Blog, {
Blog: ['*'],
Entry: [peewee.Count('pk')]
}).group_by('id').join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
[]))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
[]))
sq = sq.order_by(('count', 'desc'))
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc',
[]))
def test_selecting_with_aggregation(self):
sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) AS count").group_by("id").join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
[],
),
)
sq = sq.having("count > 2")
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
[],
),
)
sq = SelectQuery(Blog, {Blog: ["*"], Entry: [peewee.Count("pk")]}).group_by("id").join(Entry)
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
[],
),
)
sq = sq.having("count > 2")
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
[],
),
)
sq = sq.order_by(("count", "desc"))
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc",
[],
),
)
def test_selecting_with_aggregation(self):
sq = SelectQuery(Blog, 't1.*, COUNT(t2.id) AS count').group_by('id').join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
sq = SelectQuery(Blog, {
Blog: ['*'],
Entry: [peewee.Count('id')]
}).group_by('id').join(Entry)
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
sq = sq.order_by(('count', 'desc'))
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc', []))
