Hi all, After reading future of QuerySet.extra() <https://groups.google.com/forum/#!topic/django-developers/FojuU0syO8Y> discussion, I was thinking about the current custom ways to tweak SQL queries in Django. Firstly, it was the current .extra method, which everyone likely wants to get rid of. Secondly, performing raw SQL queries from queryset manager or even directly from cursor. It's really a great approach to perform raw SQL and still receive objects of the given model at the end, I think. However, the main disadvantage of the current implementation - it's required to type in real table/field names, which destroys model's abstraction level (when you never reference tables/columns by their real names). For such cases it's great to have DSL to build SQL queries, but it's a big question how low- or high-level language should be. Anyway, it ended up with the idea to have SQL templates, similar to current Django HTML templates. I even implemented a sample as proof of the concept:
https://github.com/Nepherhotep/django-orm-sugar/blob/exec_sql_template/tests/sql_template_test_app/app/tests.py https://github.com/Nepherhotep/django-orm-sugar/blob/exec_sql_template/tests/sql_template_test_app/app/templates/select_user_profile.sql How it works - there is a template "select_user_profile.sql" in a standard templates folder: {% autoescape off %} SELECT * FROM {{ model }} WHERE {{ model.name }} = {{ search_term }} {% endautoescape %} I had to put autoescape tags to prevent quotes in model name converted into HTML entities. A function below should be called somewhere in views: raw_queryset = exec_sql_template(UserProfile, "select_user_profile.sql", {'search_term': 'Bob'}) This function automatically creates a variable "model" (which is actually a special model wrapper, which prints table or column names) and passes it into template context. To prevent SQL injection, passed params converted into %(variable_name)s representation, and escaped automatically by driver. Thus the solution above doesn't work with SQLite (as it doesn't support named variables). Also I wanted to automatically provide aliases for all existing models in form <app label>.<model name> (not implemented yet): SELECT * FROM {{ model }} LEFT OUTER JOIN {{ myapp.ModelName }} ON {{ myapp.ModelName.profile }} = {{ model.pk }} Almost everything can work out of the box using standard Django templates, except quotes converted into entities, unless they don't disabled explicitly. Also, not sure if all the databases support 'tablename'.'name' syntax to safely define table/column, otherwise wrapper classes should be engine dependent. I don't suggest a concrete implementation, just wanted to get responses of the concept. Regards, Alexey -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/da716f91-e5ab-466b-8d3e-475cd3738c12%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
