Django model based on an SQL table-valued function using MyModel.objects.raw()

Question

If it's relevant I'm using Django with Django Rest Framework, django-mssql-backend and pyodbc

I am building some read only models of a legacy database using fairly complex queries and Django's MyModel.objects.raw() functionality. Initially I was executing the query as a Select query which was working well, however I received a request to try and do the same thing but with a table-valued function from within the database.

Executing this:

MyModel.objects.raw(select * from dbo.f_mytablefunction)

Gives the error: Invalid object name 'myapp_mymodel'. Looking deeper into the local variables at time of error it looks like this SQL is generated:

'SELECT [myapp_mymodel].[Field1], '
 '[myapp_mymodel].[Field2] FROM '
 '[myapp_mymodel] WHERE '
 '[myapp_mymodel].[Field1] = %s'

The model itself is mapped properly to the query as executing the equivalent:

MyModel.objects.raw(select * from dbo.mytable)

Returns data as expected, and dbo.f_mytablefunction is defined as:

CREATE FUNCTION dbo.f_mytablefunction
(
     @param1 = NULL etc etc
)
RETURNS TABLE
AS
RETURN
(
    SELECT
            field1, field2 etc etc
        FROM            
            dbo.mytable
)

If anyone has any explanation as to why these two modes of operation are treated substantially differently then I would be very pleased to find out.

Answer 1

Guess you've figured this out by now (see docs):

MyModel.objects.raw('select * from dbo.f_mytablefunction(%s)', [1])

If you'd like to map your table valued function to a model, this gist has a quite thorough approach, though no license is mentioned.

Once you've pointed your model 'objects' to the new TableFunctionManager and added the 'function_args' OrderedDict (see tests in gist), you can query it as follows:

MyModel.objects.all().table_function(param1=1)

For anyone wondering about use cases for table valued functions, try searching for 'your_db_vendor tvf'.