DISTINCT ON fields is not supported by this database backend

Question

I am using distinct to get the distinct latest values but it is giving me an error:

DISTINCT ON fields is not supported by this database backend

views.py

class ReportView(LoginRequiredMixin, generic.TemplateView):
    template_name = 'admin/clock/report.html'

    def get_context_data(self, **kwargs):
        context = super(ReportView, self).get_context_data(**kwargs)
        context['reports'] =  TimesheetEntry.objects.filter(
                                  timesheet_jobs__job_company = self.request.user.userprofile.user_company,
                              ).distinct('timesheet_users')
        return context

Basically I want to query on TimesheetEntry model where there will be lot of entries of user which is a foreign key in User in-built model.

So I want to query with distinct user so that latest entry of the user will be displayed. It is very important for me to get the latest entry of user.

models.py

class TimesheetEntry(models.Model):
    timesheet_users = models.ForeignKey(User, on_delete=models.CASCADE,related_name='timesheet_users')
    timesheet_jobs = models.ForeignKey(Jobs, on_delete=models.CASCADE,related_name='timesheet_jobs')
    timesheet_clock_in_date = models.DateField()
    timesheet_clock_in_time = models.TimeField()

Eachelle

What database do you use? – Primarily

@SergeyPugach I am using MySQL. – Eachelle

It won't work for MySQL, it works for PostgreSQL only. – Primarily

Answer 1

distinct('field_name') is not supported in MySQL. It only support distinct(). distinct('field_name') will only work on PostgresSQL. For more details, please check the documentation.

Examples (those after the first will only work on PostgreSQL):(Copy Pasted from Documentation:)

>>> Author.objects.distinct() 
   [...]

>>> Entry.objects.order_by('pub_date').distinct('pub_date')
   [...]

>>> Entry.objects.order_by('blog').distinct('blog')
   [...]

>>> Entry.objects.order_by('author', 'pub_date').distinct('author', 'pub_date')
   [...]

>>> Entry.objects.order_by('blog__name', 'mod_date').distinct('blog__name', 'mod_date')
   [...]

>>> Entry.objects.order_by('author', 'pub_date').distinct('author')
   [...]

Schoolfellow

can you please tell me how can i query for MySQL? – Eachelle

You can look into this SO answer: #12382732 – Schoolfellow

Answer 2

In MySQL you could use .values() to limit your data selection by a field first and then use .distinct() method. example below:

Author.objects.values("ref_number").distinct() 

Sanctimonious

Answer 3

May you should this one

>>> queryset = TimesheetEntry.objects.distinct()
>>> context['reports'] = queryset .filter(timesheet_jobs__job_company = self.request.user.userprofile.user_company, )

Gorgonian