I am asking if a particular feature is available in PonyORM, and if it is not, then I am requesting this feature.
The feature is this: using some method other than raw_sql() to generate a query with column aliases. That is, it should be possible to generate a query whose SQL contains the word "AS."
Python that works:
query = select((coursePerson.courseSession.course.title, coursePerson.courseSession.session.title) for coursePerson in CoursePerson)
print(query.get_sql())
Generates the following SQL:
SELECT DISTINCT "course"."title", "session"."title"
FROM "CoursePerson" "courseperson", "CourseSession" "coursesession", "Course" "course", "TrainingSlot" "session"
WHERE "courseperson"."classtype" IN ('Attendee', 'Registrant', 'CoursePerson')
AND "courseperson"."courseSession" = "coursesession"."id"
AND ("coursesession"."course" = "course"."id")
AND ("coursesession"."session" = "session"."id")
If I use the raw_sql() function, I lose the joined entities:
Python:
query = select((raw_sql('"course"."title" AS "course_title"'), raw_sql('"session"."title" AS "session_title"')) for coursePerson in CoursePerson)
print(query.get_sql())
SQL:
SELECT DISTINCT "course"."title" AS "course_title", "session"."title" AS "session_title"
FROM "CoursePerson" "courseperson"
WHERE "courseperson"."classtype" IN ('Attendee', 'Registrant', 'CoursePerson')
The SQL that I want:
SELECT DISTINCT "course"."title" AS "course_title", "session"."title" AS "session_title"
FROM "CoursePerson" "courseperson", "CourseSession" "coursesession", "Course" "course", "TrainingSlot" "session"
WHERE "courseperson"."classtype" IN ('Attendee', 'Registrant', 'CoursePerson')
AND "courseperson"."courseSession" = "coursesession"."id"
AND ("coursesession"."course" = "course"."id")
AND ("coursesession"."session" = "session"."id")
In my experience, column aliasing is very common in SQL queries. If it is not a feature of PonyORM, then I would understand why if it were not supported in all dialects of SQL that PonyORM supports. However, as it happens, it is supported in all those dialects. Here is the relevant documentation for each:
SQLite: view the diagram of the "select-stmt" scheme, and then click the "Show" button next to the "result_column" scheme to show that SQLite supports column aliasing with AS
Again, if this is not already a feature in PonyORM, then it would be a useful feature to have, not only to me, but I believe to most people. If it is already in PonyORM, then please explain to me how it can be done without using raw_sql().
I am asking if a particular feature is available in PonyORM, and if it is not, then I am requesting this feature.
The feature is this: using some method other than
raw_sql()to generate a query with column aliases. That is, it should be possible to generate a query whose SQL contains the word "AS."Python that works:
Generates the following SQL:
If I use the
raw_sql()function, I lose the joined entities:Python:
SQL:
The SQL that I want:
In my experience, column aliasing is very common in SQL queries. If it is not a feature of PonyORM, then I would understand why if it were not supported in all dialects of SQL that PonyORM supports. However, as it happens, it is supported in all those dialects. Here is the relevant documentation for each:
Again, if this is not already a feature in PonyORM, then it would be a useful feature to have, not only to me, but I believe to most people. If it is already in PonyORM, then please explain to me how it can be done without using
raw_sql().Thank you!!!