Adding raw SQL support?

[shuki25]

Would it be possible to add support for RawQuerySet data type if we use raw SQL instead of Django's OMS? For example Manager.objects.raw_as_qs("select * from mytable")? My project has complex SQL statements for report generation and would like to be able to use raw SQL.

For example, code snippet below:

from django.db import connection
from django.db.models import Manager, Model
from ajax_datatable.views import AjaxDatatableView

class MyManager(Manager):
    def raw_as_qs(self, raw_query, params=()):
        """Execute a raw query and return a QuerySet.  The first column in the
        result set must be the id field for the model.
        :type raw_query: str | unicode
        :type params: tuple[T] | dict[str | unicode, T]
        :rtype: django.db.models.query.QuerySet
        """
        cursor = connection.cursor()
        try:
            cursor.execute(raw_query, params)
            return self.filter(id__in=(x[0] for x in cursor))
        finally:
            cursor.close()

class RawModel(Model):
    objects = MyManager()

And for the ajax call:

class AjaxJournalLog(AjaxDatatableView):
    model = RawModel
    title = "Journal Log"
    initial_order = [["game_start", "asc"]]
    length_menu = [[10, 20, 50, 100], [10, 20, 50, 100]]
    search_values_separator = "+"

    column_defs = [
        AjaxDatatableView.render_row_tools_column_def(),
        {"name": "id", "visible": False},
        {"name": "file", "visible": True},
        {"name": "game_start", "visible": True},
        {"name": "game_end", "visible": True},
        {"name": "progress_code", "visible": True},
        {"name": "rows_processed", "visible": True},
        {"name": "parser_time", "visible": True},
    ]

    def get_initial_queryset(self, request=None):
        return RawModel.objects.raw_as_qs("select * from journal_log")

Here's the traceback:

Traceback (most recent call last):
  File "/venv/lib/python3.8/site-packages/asgiref/sync.py", line 458, in thread_handler
    raise exc_info[1]
  File "/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 38, in inner
    response = await get_response(request)
  File "/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 233, in _get_response_async
    response = await wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/venv/lib/python3.8/site-packages/asgiref/sync.py", line 423, in __call__
    ret = await asyncio.wait_for(future, timeout=None)
  File "/Users/josh/opt/anaconda3/lib/python3.8/asyncio/tasks.py", line 455, in wait_for
    return await fut
  File "/venv/lib/python3.8/site-packages/asgiref/current_thread_executor.py", line 22, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/venv/lib/python3.8/site-packages/asgiref/sync.py", line 462, in thread_handler
    return func(*args, **kwargs)
  File "/venv/lib/python3.8/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/venv/lib/python3.8/site-packages/django/utils/decorators.py", line 43, in _wrapper
    return bound_method(*args, **kwargs)
  File "/venv/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/venv/lib/python3.8/site-packages/ajax_datatable/views.py", line 405, in dispatch
    response = super(AjaxDatatableView, self).dispatch(request, *args, **kwargs)
  File "/venv/lib/python3.8/site-packages/django/views/generic/base.py", line 98, in dispatch
    return handler(request, *args, **kwargs)
  File "/venv/lib/python3.8/site-packages/ajax_datatable/views.py", line 491, in post
    return self.get(request, *args, **kwargs)
  File "/venv/lib/python3.8/site-packages/ajax_datatable/views.py", line 520, in get
    qs = self.prepare_queryset(params, qs)
  File "/venv/lib/python3.8/site-packages/ajax_datatable/views.py", line 770, in prepare_queryset
    qs = self.sort_queryset(params, qs)
  File "/venv/lib/python3.8/site-packages/ajax_datatable/views.py", line 788, in sort_queryset
    qs = qs.order_by(
  File "/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1149, in order_by
    obj.query.add_ordering(*field_names)
  File "/venv/lib/python3.8/site-packages/django/db/models/sql/query.py", line 1991, in add_ordering
    self.names_to_path(item.split(LOOKUP_SEP), self.model._meta)
  File "/venv/lib/python3.8/site-packages/django/db/models/sql/query.py", line 1537, in names_to_path
    raise FieldError("Cannot resolve keyword '%s' into field. "
django.core.exceptions.FieldError: Cannot resolve keyword 'game_start' into field. Choices are: id

I don't think it would work well when converting RawQuerySet to QuerySet, it just introduces new problems. I'm thinking we may have to create a separate class to deal with raw SQL. I like your package and would love to use it with raw SQL as well.

I could make a PR to support that if you could direct me to the best starting point.

Cheers, Josh

[morlandi]

Josh, since AjaxDatatableView uses so many properties of the QuerySet for filtering, sorting ... and so on, this doesn't seem an easy task. I have little experience with RawQuerySet and can't figure out an easy solution to suggest.

However, let me describe a possible alternative approach.

(1) You could save your complex SQL statements in a DB View:

DROP VIEW IF EXISTS "reports_whatever";

CREATE VIEW "reports_whatever" AS
    ... sql statement here ...

The above script could be kept in you repo, and run multiple times to recreate the View any time your SQL changes; conceptually: at deployment time.

(2) In models.py, define a Model with a list of fields which mimics the columns produced by the previous VIEW:

class Whatever(models.Model):

    class Meta:
        managed = False

    ... fields ...

The important detail above is managed = False, which excludes this model from Django's migrations.

(3) With this setup, Whatever exposes a proper QuerySet, still delegating data extraction to you complex SQL statement.

[shuki25]

Actually using views would work. Why didn't I think of that in the first place? lol

Thanks for your response!

[morlandi]

you're welcome ;)