def test_select_with_models(self):
sq = SelectQuery(Blog, {Blog: '*'})
self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']})
self.assertEqual(sq.sql(), ('SELECT title, id FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
sq = SelectQuery(Blog, {
Blog: ['title', 'id'],
Entry: [peewee.Count('pk')]
}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
sq = SelectQuery(Blog, {
Blog: ['title', 'id'],
Entry: [peewee.Max('pk')]
}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id, MAX(t2.pk) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
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_ordering_across_joins(self):
a, a1, a2, b, b1, b2, t1, t2 = self.get_common_objects()
b3 = self.create_entry(title='b3', blog=b)
c = self.create_blog(title='c')
c1 = self.create_entry(title='c1', blog=c)
sq = Blog.select().join(Entry).order_by(
peewee.desc('title')).group_by('blog_id')
self.assertEqual(list(sq), [c, b, a])
sq = Blog.select().join(Entry).order_by(
peewee.desc('title')).distinct()
self.assertEqual(list(sq), [c, b, a])
sq = Blog.select().where(title__in=['a', 'b']).join(Entry).order_by(
peewee.desc('title')).group_by('blog_id')
self.assertEqual(list(sq), [b, a])
sq = Blog.select().where(title__in=['a', 'b']).join(Entry).order_by(
peewee.desc('title')).distinct()
self.assertEqual(list(sq), [b, a])
sq = Blog.select('t1.*, COUNT(t2.pk) AS count').join(Entry).order_by(
peewee.desc('count')).group_by('blog_id')
qr = list(sq)
self.assertEqual(qr, [b, a, c])
self.assertEqual(qr[0].count, 3)
self.assertEqual(qr[1].count, 2)
self.assertEqual(qr[2].count, 1)
sq = Blog.select({
Blog: ['*'],
Entry: [peewee.Count('pk', 'count')]
}).join(Entry).group_by('blog_id').order_by(peewee.desc('count'))
qr = list(sq)
self.assertEqual(qr, [b, a, c])
self.assertEqual(qr[0].count, 3)
self.assertEqual(qr[1].count, 2)
self.assertEqual(qr[2].count, 1)
def test_nullable_fks(self):
a = self.create_blog(title='a')
b = self.create_blog(title='b')
c = self.create_blog(title='c')
user_a = User(username='******', blog=a)
user_a.save()
user_a2 = User(username='******', blog=a)
user_a2.save()
user_b = User(username='******', blog=b)
user_b.save()
sq = Blog.select({
Blog: ['*'],
User: [peewee.Count('id', 'count')]
}).join(User).group_by('blog_id').order_by(peewee.desc('count'))
qr = list(sq)
self.assertEqual(qr, [a, b, c])
self.assertEqual(qr[0].count, 2)
self.assertEqual(qr[1].count, 1)
self.assertEqual(qr[2].count, 0)