CODEWITHSUNDEEP
django
user1470034
user1470034

Reputation: 691

Django DISTINCT issue related to database

In my database I have a report_id that is 100, but it has two parts to the report_id which have nothing to do with my application. I was able to use .distinct() in the example below. Which worked great at one point.

In the example below my data returns correctly in my form, but I need to POST the data with an ID that doesn't exist in this first example.

reportaccess = QvReportList.objects.filter(report_id__in= reportIds).values_list('report_name_sc', flat = True).distinct()

I had to change my query set to the following to allow the report_id to POST to the next view.

currreportaccess = QvReportList.objects.filter(report_id__in= reportIds).distinct()

My problem is because I no longer have values_list with a single field as flat. It's coming back as two rows as distinct.

My HTML is defined as the following:

{% for app in currreportaccess %}

              <li> <input type="checkbox" name="current_report" value ="{{app.report_id}}" >  {{ app.report_name_sc }}</li>

          {% endfor %}

Is it possible to get a distinct in this situation? If so, how should I go about it?

I've added my model for the table QVReportList.

class QvReportList(models.Model):
    qv_dept_id = models.CharField(db_column='QV_Dept_ID', max_length=100)  # Field name made lowercase.
    report_id = models.CharField(db_column='Report_ID',primary_key=True, max_length = 100, serialize=False)  # Field name made lowercase.
    report_name = models.CharField(db_column='Report_Name', max_length=255, blank=True, null=True)  # Field name made lowercase.
    report_name_sc = models.CharField(db_column='Report_Name_SC', max_length=255, blank=True, null=True)  # Field name made lowercase.
    qv_filename = models.CharField(db_column='QV_FileName', max_length=255, blank=True, null=True)  # Field name made lowercase.
    report_access = models.CharField(db_column='Report_Access', max_length=20, blank=True, null=True)  # Field name made lowercase.
    report_group_id = models.IntegerField(db_column='Report_Group_ID', blank=True, null=True)  # Field name made lowercase.
    report_sub_group_id = models.IntegerField(db_column='Report_Sub_Group_ID', blank=True, null=True)  # Field name made lowercase.
    load_date = models.DateTimeField(db_column='Load_Date', blank=True, null=True)  # Field name made lowercase.
    approver_fname = models.CharField(db_column='Approver_FName', max_length=255, blank=True, null=True)  # Field name made lowercase.
    approver_lname = models.CharField(db_column='Approver_LName', max_length=255, blank=True, null=True)  # Field name made lowercase.
    approver_ntname = models.CharField(db_column='Approver_NTName', max_length=255, blank=True, null=True)  # Field name made lowercase.
    beg_date = models.DateTimeField(db_column='Beg_Date', blank=True, null=True)  # Field name made lowercase.
    end_date = models.DateTimeField(db_column='End_Date', blank=True, null=True)  # Field name made lowercase.
    active = models.IntegerField(db_column='Active', blank=True, null=True)  # Field name made lowercase.
    approval_id = models.IntegerField(db_column='Approval_ID', blank=True, null=True)  # Field name made lowercase.
    role_based_id = models.IntegerField(db_column='Role_Based_ID', blank=True, null=True)  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'QV_Report_List'

The issue is with the field report_access for report ID '100' there is a SUMMARY and Patient value for report_access.

I can't just call distinct on the field name because i'm required to use SQL server and can't switch to posgress. I get the following error message.

DISTINCT ON fields is not supported by this database backend

Upvotes: 0

Views: 52

Answers (1)

Andrei Berenda
Andrei Berenda

Reputation: 1986

you can use .values() insted of .values_list() like this:

QvReportList.objects.filter(
    report_id__in= reportIds
).values('report_id', 'report_name_sc'
).distinct()

values() returns dict (or something like that) and in you template you can get like you have written.

Upvotes: 1

Related Questions