How to ordering SQL result in Union peewee orm

Question

I use peewee ORM for connecting to MySQL server.

I wrote this code:

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()

this query result correct data when i remove .order_by(Comments.date_submit). Now i have a problem in ordering.

the sql peewee statment is:

<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']>

and error is:

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

how i can reorder this query?

Answer 1

You can try this:

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