1

Consider I'm trying to represent a simple query at this:

SELECT student.name, MAX(grades.final_grade) as max_grade
From grades , students
where student.id == grade.student_id and max_grade == 90

My question is focues on syntax and how to represent the same raw sql query.

In sqlalchemy orm it may look like this: Student and Grade representing tables as orm objects.

query= session.query(Student.name , func.max(Grade.final_grade).join(Grade, Student.id == Grade.student_id).filter(text("max_grade = 90")).all()

But in this case I get excetion:

{Operationalerror}{_mysql_exceptions.Operationalerror)(1054, "Unknown column 'max_grade' in where clause"

Any idea how to solve this, why isn't my label recognized?
Text construct usage manual reference is here

Edit: question about raw sql, since we got a feeling that something isn't right.

My original sql query was:

SELECT student.name, MAX(grades.grade) as grade
From grades , students
where student.id = grade.student_id and grade = 90

In the where clause grade=90 was referring to grade from table grades, meaning grades.grade =90 and in no way to MAX(grades.grade)=90?
So query worked because grades was referring to table column rather than label in select clause?

Thank you

Community
  • 1
  • 1
pythonguy
  • 11
  • 4
  • Please provide a proper [mcve]. Include the models for a start. The error is the result of trying to use a select list item in the where clause, among other things. You can't do that. Read https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ as to why. – Ilja Everilä Dec 02 '17 at 15:52
  • @IljaEverilä: If I got you right, you are saying the original sql(not sqlalchemy) is not correct but it seems to work, why can't we reference a select element when labeled in where clause? – pythonguy Dec 02 '17 at 16:10
  • The "where" happens before "select". A rough order of operations would be from, where, group by, having, select, order by (omitting many steps along the way). So because the "where" is performed before the "select", you simply cannot refer to select list items in the where. – Ilja Everilä Dec 02 '17 at 16:21
  • @IljaEverilä: I think I got some understanding why I had it "worked", please see my edit, I would appreciate your thoughts. – pythonguy Dec 02 '17 at 17:05
  • "So query worked because grades was referring to table column rather than label in select clause?" That is correct. Note that using an aggregate such as MAX the way you're using it without a grouping, together with non aggregates, is a MySQL (mis)feature that might bite you. – Ilja Everilä Dec 02 '17 at 17:12
  • https://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause – Ilja Everilä Dec 02 '17 at 17:18
  • Possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) – Ilja Everilä Dec 02 '17 at 17:23
  • @IljaEverilä: Thank you very much! – pythonguy Dec 02 '17 at 18:42

0 Answers0