python - PeeWee 对整数值进行排序，就好像它们是字符串一样

Question

month_column = dc.Message.date_utc.month
year_column = dc.Message.date_utc.year
message_count = fn.Count(dc.Message.id)
yearmonthname_column = year_column.concat("_").concat(month_column).concat("_").concat(dc.User.name)
yearmonth_column = year_column.concat("_").concat(month_column)
query = (dc.Message
         .select(yearmonthname_column.alias('yearmonthname'), yearmonth_column.alias('yearmonth'), dc.User.name, message_count.alias("messages_count"), year_column.alias("year"), month_column.alias("month"))
         .join(dc.User)
         .where(dc.Message.posted_in == chatId)
         .group_by(yearmonthname_column)
         .order_by(message_count.desc())
         .order_by(month_column.cast('INTEGER')) # SORTING BY MONTH
         .order_by(year_column)
         )

在上面，你可以看到我的查询。这里的问题是:我希望它按 month 值排序，就好像它是数字一样，但我看到的结果如下:

"1", "10", "11", "12", "2"

代替:

"1", "2", "10", "11", "12" 

按月排序发生在这一行:

.order_by(month_column.cast('INTEGER'))

如您所见，我试图将此值转换为 INTEGER，但它实际上并没有改变任何东西。

是否有解决此问题的简单方法？

编辑:

我还注意到在 peewee 生成的最终 SQL 代码中没有 cast。

我是如何得到它的:

print(query.sql())

它的样子:

('SELECT ((((date_part(?, "t1"."date_utc") || ?) || date_part(?, "t1"."date_utc")) || ?) || "t2"."name") AS yearmonthname, ((date_part(?, "t1"."date_utc") || ?) || date_part(?, "t1"."date_utc")) AS yearmonth, "t2"."name", Count("t1"."id") AS messages_count, date_part(?, "t1"."date_utc") AS year, date_part(?, "t1"."date_utc") AS month FROM "message" AS t1 INNER JOIN "user" AS t2 ON ("t1"."created_by_id" = "t2"."id") WHERE ("t1"."posted_in_id" = ?) GROUP BY ((((date_part(?, "t1"."date_utc") || ?) || date_part(?, "t1"."date_utc")) || ?) || "t2"."name") ORDER BY date_part(?, "t1"."date_utc")', ['year', '_', 'month', '_', 'year', '_', 'month', 'year', 'month', 220, 'year', '_', 'month', '_', 'year'])

这可能就是它不起作用的原因。有谁知道为什么会这样？

Answer 1

.order_by(Message.month.cast('int')) 仅适用于 Peewee v3.0.0 或更高版本。如果您使用的是 v2.x，则必须使用 Cast()如 .order_by(Cast(Message.month, 'int'))。 cast() 方法添加了 this commit .

这是一个工作代码:

import datetime
import peewee
from peewee import *
from pprint import pprint

db = SqliteDatabase('/tmp/a.db')

class Message(Model):
    month = CharField()
    class Meta:
        database = db

db.connect()
db.drop_tables(models=[Message], safe=True)
db.create_tables([Message])

Message.create(month="7")
Message.create(month="8")
Message.create(month="9")
Message.create(month="10")
Message.create(month="11")
Message.create(month="12")
Message.create(month="1")
Message.create(month="2")
Message.create(month="3")
Message.create(month="4")
Message.create(month="5")
Message.create(month="6")

m = []
query = Message.select().order_by(Message.month)
for q in query:
    m.append(q.month)
pprint(m) #=> ['1', '10', '11', '12', '2', '3', '4', '5', '6', '7', '8', '9']

m = []
query = Message.select().order_by(Message.month.cast("INTEGER"))
for q in query:
    m.append(q.month)
pprint(m) #=> ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']

# for Peewee 2.x
m = []
query = Message.select().order_by(Cast(Message.month, 'int'))
for q in query:
    m.append(q.month)
pprint(m) #=> ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']

pprint(query.sql())
#=> ('SELECT "t1"."id", "t1"."month" FROM "message" AS "t1" ORDER BY '
#    'CAST("t1"."month" AS int)',
#    [])

print(peewee.__version__) #=> 3.0.18