Applying distinct sometimes results in errors

[JackAtOmenApps]

I'm using postgresql as my database.

The example works perfectly as-is. Additionally, if I change

class AlbumViewSet(viewsets.ModelViewSet):
    queryset = Album.objects.all().order_by('rank')
    serializer_class = AlbumSerializer

to

class AlbumViewSet(viewsets.ModelViewSet):
    queryset = Album.objects.all().order_by('rank').distinct('rank')
    serializer_class = AlbumSerializer

or

class AlbumViewSet(viewsets.ModelViewSet):
    queryset = Album.objects.all().order_by('id').distinct('id')
    serializer_class = AlbumSerializer

everything works as expected. I get object instances with distinct rank or id.

But, if instead I set distinct to 'year', which just like rank is a PositiveIntegerField in the model:

class AlbumViewSet(viewsets.ModelViewSet):
    queryset = Album.objects.all().order_by('year').distinct('year')
    serializer_class = AlbumSerializer

...I run into problems. What's weird is that in the DRF API viewer, everything looks great. I get the expected resulting filtered queryset.

{"data":[{"DT_RowId":"row_12","DT_RowAttr":{"data-pk":12},"rank":12,"name":"Kind of Blue","year":1959,"artist_name":"Miles Davis","genres":"Modal","artist":{"id":9,"name":"Miles Davis"}},{"DT_RowId":"row_5","DT_RowAttr":{"data-pk":5},"rank":5,"name":"Rubber Soul","year":1965,"artist_name":"The Beatles","genres":"Pop Rock","artist":{"id":2,"name":"The Beatles"}},{"DT_RowId":"row_9","DT_RowAttr":{"data-pk":9},"rank":9,"name":"Blonde on Blonde","year":1966,"artist_name":"Bob Dylan","genres":"Folk Rock, Rhythm & Blues","artist":{"id":4,"name":"Bob Dylan"}},{"DT_RowId":"row_1","DT_RowAttr":{"data-pk":1},"rank":1,"name":"Sgt. Pepper's Lonely Hearts Club Band","year":1967,"artist_name":"The Beatles","genres":"Psychedelic Rock, Rock & Roll","artist":{"id":2,"name":"The Beatles"}},{"DT_RowId":"row_10","DT_RowAttr":{"data-pk":10},"rank":10,"name":"The Beatles ("The White Album")","year":1968,"artist_name":"The Beatles","genres":"Experimental, Pop Rock, Psychedelic Rock, Rock & Roll","artist":{"id":2,"name":"The Beatles"}},{"DT_RowId":"row_14","DT_RowAttr":{"data-pk":14},"rank":14,"name":"Abbey Road","year":1969,"artist_name":"The Beatles","genres":"Classic Rock, Pop Rock, Psychedelic Rock","artist":{"id":2,"name":"The Beatles"}},{"DT_RowId":"row_6","DT_RowAttr":{"data-pk":6},"rank":6,"name":"What's Going On","year":1971,"artist_name":"Marvin Gaye","genres":"Soul","artist":{"id":5,"name":"Marvin Gaye"}},{"DT_RowId":"row_7","DT_RowAttr":{"data-pk":7},"rank":7,"name":"Exile on Main St.","year":1972,"artist_name":"The Rolling Stones","genres":"Blues Rock, Classic Rock, Rock & Roll","artist":{"id":6,"name":"The Rolling Stones"}},{"DT_RowId":"row_11","DT_RowAttr":{"data-pk":11},"rank":11,"name":"The Sun Sessions","year":1976,"artist_name":"Elvis Presley","genres":"Rock & Roll","artist":{"id":8,"name":"Elvis Presley"}},{"DT_RowId":"row_8","DT_RowAttr":{"data-pk":8},"rank":8,"name":"London Calling","year":1979,"artist_name":"The Clash","genres":"New Wave, Punk","artist":{"id":7,"name":"The Clash"}}],"recordsFiltered":10,"recordsTotal":10,"draw":1}

But the DataTable fails to load and gives an error:

DataTables warning: table id=albums - Ajax error. For more information about this error, please see http://datatables.net/tn/7

When I use Chrome's inspector, it looks like DataTables is trying to load this URL:

http://127.0.0.1:8000/api/albums/?format=datatables&draw=1&columns%5B0%5D%5Bdata%5D=rank&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=false&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=artist.name&columns%5B1%5D%5Bname%5D=artist.name&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=name&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=year&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=genres&columns%5B4%5D%5Bname%5D=genres.name&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=false&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1531170631897

...which results in an internal server error. Here's the entire resulting trace if I go to that url: https://pastebin.com/U8jbeXAA

[izimobil]

Hi,
If the JSON returned is correct, chances are your problem is on the client side code. Maybe you're not building your Datatable correctly. Good luck.

[JackAtOmenApps]

I'm seeing the problem in both the provided example and in my own project. Before using distinct everything works, but when applying distinct things go awry.

By playing around with things I did find that the reason rank works, but year doesn't is that rank is the first column called in the table. If I filter by distinct('year') and then set columns in the DataTable like this:

"columns": [
    {"data": "year"},
    {"data": "rank", "searchable": false},
    // Use dot notation to reference nested serializers.
    // This data: could alternatively be displayed with the serializer's ReadOnlyField as well, as seen in the minimal example.
    {"data": "artist.name", "name": "artist.name"},
    {"data": "name"},
    {"data": "genres", "name": "genres.name", "sortable": false},
],

then I get a valid DataTable. Whatever field is distinct must be the first field called in the table. The I can use ColReorder to move the columns around as needed.

The problem now is that while I get a table now, I can ONLY sort by the column that is distinct. Attempting to sort by any other column (using the table headers) returns ajax errors, both in the (modified) example project and in my own project.

[JackAtOmenApps]

I seem to have found a solution, though I don't know why it works.

If I change:

class AlbumViewSet(viewsets.ModelViewSet):
    queryset = Album.objects.all().order_by('year', 'rank').distinct('year')

    serializer_class = AlbumSerializer

to:

class AlbumViewSet(viewsets.ModelViewSet):
    album = Album.objects.all().order_by('year').distinct('year')
    queryset = Album.objects.filter(id__in=album)

    serializer_class = AlbumSerializer

... all works perfectly! Sorting, filtering, etc all work now. An extra query is required, but it's worth it. Still not sure why simply applying distinct fails to work properly, but I can live with this modification