def test_selecting_with_ordering(self):
sq = SelectQuery(Blog).order_by('title')
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title ASC', []))
sq = SelectQuery(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title DESC', []))
sq = SelectQuery(Entry).order_by(peewee.desc('title')).join(Blog).where(title='a')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC', ['a']))
sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC', []))
def test_selecting_with_ordering(self):
sq = SelectQuery(Blog).order_by('title')
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title ASC', []))
sq = SelectQuery(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title DESC', []))
sq = SelectQuery(Entry).order_by(peewee.desc('title')).join(Blog).where(title='a')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC', ['a']))
sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC', []))
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)
def test_ordering_on_aggregates(self):
sq = SelectQuery(Blog,
't1.*, COUNT(t2.pk) as count').join(Entry).order_by(
peewee.desc('count'))
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 ORDER BY count DESC',
[]))
def test_ordering_on_aggregates(self):
sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) as count").join(Entry).order_by(peewee.desc("count"))
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 ORDER BY count DESC",
[],
),
)
def test_model_select(self):
a = self.create_blog(title='a')
b = self.create_blog(title='b')
c = self.create_blog(title='c')
qr = Blog.select().order_by('title')
self.assertEqual(list(qr), [a, b, c])
qr = Blog.select().where(title__in=['a', 'c']).order_by(peewee.desc('title'))
self.assertEqual(list(qr), [c, a])
self.assertQueriesEqual([
('INSERT INTO blog (title) VALUES (?)', ['a']),
('INSERT INTO blog (title) VALUES (?)', ['b']),
('INSERT INTO blog (title) VALUES (?)', ['c']),
('SELECT * FROM blog ORDER BY title ASC', []),
('SELECT * FROM blog WHERE title IN (?,?) ORDER BY title DESC', ['a', 'c']),
])
def test_model_select(self):
a = self.create_blog(title='a')
b = self.create_blog(title='b')
c = self.create_blog(title='c')
qr = Blog.select().order_by('title')
self.assertEqual(list(qr), [a, b, c])
qr = Blog.select().where(title__in=['a', 'c']).order_by(peewee.desc('title'))
self.assertEqual(list(qr), [c, a])
self.assertQueriesEqual([
('INSERT INTO blog (title) VALUES (?)', ['a']),
('INSERT INTO blog (title) VALUES (?)', ['b']),
('INSERT INTO blog (title) VALUES (?)', ['c']),
('SELECT * FROM blog ORDER BY title ASC', []),
('SELECT * FROM blog WHERE title IN (?,?) ORDER BY title DESC', ['a', 'c']),
])
def test_model_select(self):
a = self.create_blog(title="a")
b = self.create_blog(title="b")
c = self.create_blog(title="c")
qr = Blog.select().order_by("title")
self.assertEqual(list(qr), [a, b, c])
qr = Blog.select().where(title__in=["a", "c"]).order_by(peewee.desc("title"))
self.assertEqual(list(qr), [c, a])
self.assertQueriesEqual(
[
("INSERT INTO blog (title) VALUES (?)", ["a"]),
("INSERT INTO blog (title) VALUES (?)", ["b"]),
("INSERT INTO blog (title) VALUES (?)", ["c"]),
("SELECT * FROM blog ORDER BY title ASC", []),
("SELECT * FROM blog WHERE title IN (?,?) ORDER BY title DESC", ["a", "c"]),
]
)
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)
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)
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_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_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.id) 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('id', '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)
