The django-refsql package implements a limited subset of this. You can
reference to columns in an existing ORM query using {{field}} syntax.
For example:
User.objects.annotate(ref=RefSQL("case when {{best_friend__age}} >
{{age}} then 'older' else 'younger' end"))
This would also set up a join to User's best friend model.See https://github.com/akaariai/django-refsql I don't like the template idea too much. While it might work well for some use cases, it seems that ORM + raw SQL is enough for now. For those use cases where you want to use this style, it should be possible to implement this in a third party project. If it turns out we are missing some public APIs in Django, we can consider adding some more hooks. - Anssi On Thu, Aug 27, 2015 at 10:43 PM, Alexey Zankevich <alex.zankev...@gmail.com> wrote: > Hi all, > > After reading future of QuerySet.extra() 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. -- 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/CALMtK1GcFYiRC%2BM6Zk9bDtaCp4Yk%3DnOvb_6%3DHOBgEeuCGS-hiQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
