python - 如何在 Union peewee orm 中排序 SQL 结果

Question

我使用 peewee ORM 连接到 MySQL 服务器。

我写了这段代码:

c = (
    Comments().select(
        Comments, Member.id.alias('member_id'), Member.username, Member.name, Member.family, Member.image_name
    ).join(
        Users, on=(Comments.user_key == Users.user_key)
    ).join(
        Member, on=(Member.username == Users.username).alias('member')
    ).where(
        Comments.element_key == element_key, Comments.status << [
            Comments().EnumStatus.ACCEPT, Comments().EnumStatus.REPORT
        ]
    )
    |
    Comments().select(
        Comments, Admin_users.id.alias('member_id'), Admin_users.username, Admin_users.name,
        Admin_users.family, Admin_users.image_name
    ).join(
        Users, on=(Comments.user_key == Users.user_key)
    ).join(
        Admin_users, on=(Admin_users.username == Users.username).alias('member')
    ).where(
        Comments.element_key == element_key, Comments.status << [
            Comments().EnumStatus.ACCEPT, Comments().EnumStatus.REPORT
        ]
    )
).order_by(Comments.date_submit).paginate(page, count)
print(c.sql)
c = c.execute()

当我删除 .order_by(Comments.date_submit) 时，此查询结果正确数据。现在我在订购时遇到了问题。

sql peewee 语句是:

<bound method CompoundSelect.sql of <class 'CommentSystem.models.comment_model.Comments'> SELECT `t2`.`id`, `t2`.`element_key`, `t2`.`user_key`, `t2`.`comment`, `t2`.`date_submit`, `t2`.`status`, `t4`.`id` AS member_id, `t4`.`username`, `t4`.`name`, `t4`.`family`, `t4`.`image_name` FROM `comments` AS t2 INNER JOIN `users` AS t3 ON (`t2`.`user_key` = `t3`.`user_key`) INNER JOIN `member` AS t4 ON (`t4`.`username` = `t3`.`username`) WHERE ((`t2`.`element_key` = %s) AND (`t2`.`status` IN (%s, %s))) UNION SELECT `t2`.`id`, `t2`.`element_key`, `t2`.`user_key`, `t2`.`comment`, `t2`.`date_submit`, `t2`.`status`, `t4`.`id` AS member_id, `t4`.`username`, `t4`.`name`, `t4`.`family`, `t4`.`image_name` FROM `comments` AS t2 INNER JOIN `users` AS t3 ON (`t2`.`user_key` = `t3`.`user_key`) INNER JOIN `admin_users` AS t4 ON (`t4`.`username` = `t3`.`username`) WHERE ((`t2`.`element_key` = %s) AND (`t2`.`status` IN (%s, %s))) ORDER BY `t1`.`date_submit` LIMIT 10 [u'11111', 'ACCEPT', 'REPORT', u'11111', 'ACCEPT', 'REPORT']>

错误是:

(1054, "Unknown column 't1.date_submit' in 'order clause'")

我如何重新排序这个查询？

Answer 1

你可以试试这个:

comments_query = (
    Comment.select(blah blah blah)
    |
    Comment.select(yadda yadda yadda)
)
ordered_query = comments_query.order_by(comments_query.c.date_submit)