Leveraging the ORM for very complex queries

[Suriya Subramanian]

Hello,

I have to write some complex SQL queries that I am unable to express using the ORM. I construct these complex queries by writing a few simple ORM queries, getting the SQL using QuerySet.query and combining them with various SQL operators manually. These hand-crafted queries are not very flexible because it is very easy to modify the final SQL.

My question: Is there a way to programmatically construct the complex queries. I see that I can get the generated SQL and parameters by invoking SQLCompiler.as_sql(). Can I invoke as_sql() on the individual query sets and then construct the complex query? What are some gotchas that I need to watch out for?

Thanks,

Suriya Subramanian

[Russell Keith-Magee]

Hi Suriya,

It sounds like you're looking for raw SQL queries:

https://docs.djangoproject.com/en/1.8/topics/db/sql/#performing-raw-queries

This allows you to issue a SQL query in SQL, rather than trying to bend the ORM to meet some complex query requirement.

You can't compose a raw query like a normal Django ORM query (e.g., you can't add a filter clause to an raw query), but a raw query object behaves exactly like queryset when it returns results - it is iterable, it returns full Django objects, and so on. 

Yours,

Russ Magee %-)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/b4465893-6d08-4ed3-babd-1f6e0f0f52ab%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Suriya Subramanian]

Hi Russ,

Thank you for your answer. I am aware of raw. However, that's now what I am looking for. Let me give a few examples of queries that I would like to write:

1) Window functions over an ORM query:

SELECT "date", SUM("weight__sum") OVER (ORDER BY "date")

FROM

(

    MyModel.objects.values('date').annotate(Sum('weight')).query

) T

2) Join of two ORM queries

( complex ORM query ) NATURAL JOIN ( complex ORM query  )

Writing query 1 and 2 fully in SQL is painful, since they leverage a lot of Python and ORM logic (for example: parsing URL arguments and filtering). Composing SQL and ORM, even if it means having to deal with the guts of SQLCompiler.as_sql() seems to be a decent solution for me. I am asking if there any best practices to follow or gotchas to watch out for.

Thanks,

Suriya

[Michael Manfre]

The internals of the ORM are deemed a private API and have undergone significant changes in the past without being constrained by the two release deprecation cycle. As some one who was forced to write query construction code based upon Django internals, my recommendation is to only do that if you have no other choice. You will eventually get hit by a change in a subsequent release of Django that forces you to remain on a no longer supported version of Django while you update the hack for the new version of Django or (better) rewrite it to use supported APIs.

Regards,

Michael Manfre

[Suriya Subramanian]

Thank you Russ and Michael. For the reference of other users and to get feedback, here's what I ended up doing: https://gist.github.com/anonymous/154512b369fe7e273631

import itertools
from django.db import connection
 
"""
Combine SQL and ORM.

sql = 'SELECT * FROM ( {} ) T1 NATURAL FULL OUTER JOIN ( {} ) T2; '
querysets = [
    MyModel1.objects.filter(...),
    MyModel2.objects.filter(...),
]
cursor = execute_orm_sql_query(sql, querysets)
"""
 
def sqlparams_from_queryset(qs):
    """
    Returns the SQL query and params of a queryset.
    """
    (qstr, params) = qs.query.sql_with_params()
    return (qstr, params)
 
def execute_orm_sql_query(sqltemplate, querysets):
    """
    Combine the Raw SQL with Django ORM query output and execute the query.

    Returns the database cursor.
    """
    sqlparams = [ sqlparams_from_queryset(qs) for qs in querysets ]
    (sqls, params) = zip(*sqlparams) # zip(*...) is the inverse of zip
    params = tuple(itertools.chain.from_iterable(params))
    cursor = connection.cursor()
    cursor.execute(sqltemplate.format(*sqls), params)
    return cursor

Thanks,

Suriya