Unknown column 'label_column' in 'order clause'\" SQLAlchemy when I use limit() in the query that has labels

Question

I have a query that was too big so I added limit(), but now it's breaking, it's not recognizing the column I create with label().

When i use the labels:

query = self.session.query(
  Model,
  (
    Model.date_example == Model.date_example_two
  ).label("label_column_one"),
  (
    Model.date_example_three < Model.date_example_four
  ).label("label_column_two"),
  (
    Model.date_example_five > Model.date_example_six
  ).label("label_column_three"),
)

Then i order by the label columns:

query = query.order_by(
  desc(text("label_column_one")),
  desc(text("label_column_two")),
  desc(text("label_column_three")),
)

The query with limit:

result = query.limit(5000).all()

The error:

"sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1054 (42S22): Unknown column 'label_column_one' in 'order clause'"

If I don't use limit() it works normally, I also took the SQL created by sqlalchemy and ran it in dbeaver and it worked normally!

Answer 1

Maybe try reusing the labeled special columns like this:

    col1 = (
        Model.date_example_1 == Model.date_example_2
    ).label("label_column_one")
    col2 = (
        Model.date_example_3 < Model.date_example_4
    ).label("label_column_two")
    col3 = (
        Model.date_example_5 > Model.date_example_6
    ).label("label_column_three")
    q = self.session.query(Model, col1, col2, col3)
    q = q.order_by(desc(col1), desc(col2), desc(col3))
    q = q.limit(5000)
    print (q)
    print (q.all())

I can't test this right now directly because I have 1.4 installed and have to change things to get this setup. Everything mentioned seems to work in 1.4+ but I understand it is a pain to upgrade.