Django MySQL distinct query for getting multiple values

Question

I have a MySQL database unfortunately used with Django 1.4.1. Distinct function is only working for POSTGRESQL if i get it right.

I have to make a distinct query consist of multiple values while only distinct one,

Like; This one works for POSTGRE but not with MYSQL, I get the following error;

DISTINCT ON fields is not supported by this database backend

staff = Staff.objects.order_by('person__full_name').distinct('person__full_name')

Then I tried

staff = Staff.objects.values('person__full_name','staff_job_categories').distinct().order_by('person__full_name')

But I do not get distinct values because i get also the staff job categories. But when I dont contain it I could not get it as array.

Any idea ?

Community · Accepted Answer · 2017-05-23 11:53:08Z

20

.distinct([*fields]) only works in PostgresSQL.

From distinct documentation

Here's the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.

As stated all fields in a record are checked. Mostly likely in your case you are getting records with different field values (more likely a case if you are queries multiple tables ManyToMany or ForeignKey relations).

For consolidating as array you can refer your earlier question Django Query distinct values works but i cant use the query result

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 13, 2012 at 9:34

Rohan

53.5k12 gold badges95 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

solidparallel · Accepted Answer · 2014-02-05 18:40:29Z

7

names = Staff.objects.order_by('person__full_name').values('person__full_name').distinct()

will give you distinct full names, and you can do a similar thing to get distinct job categories.

These will give you lists of values, not objects themselves, but if I interpret your question correctly then I think these will give you what you want.

answered Feb 5, 2014 at 18:40

solidparallel

911 silver badge5 bronze badges

Comments

Mahadi Hassan · Accepted Answer · 2021-08-21 15:47:37Z

Not all database support Distinct('field name'). So what we can do is use very simple problem solving skill

 class vendor_ready_distinct(APIView):
        def get(self, request):
            obj_readyproduct = vendor_list_readyProduct.objects.all().order_by('mobile_number')

            unique_list = []
            list = []
            for x in obj_readyproduct:
                # check if exists in unique_list or not
                if x.mobile_number not in list:
                    unique_list.append(x)
                    list.append(x.mobile_number)
    
            seri_obj = vendor_list_readyProductSerializer(unique_list, many=True)
            return Response(seri_obj.data)

here we are getting the distinct row by using method 1 from geeksforgeeks

Cristian Lazo · Accepted Answer · 2021-09-28 14:25:31Z

1

You can use this code if you want to use DISTINCT with WHERE statement.

staff = Staff.objects.filter(person__full_name="Alex").values('staff_job_categories').distinct().order_by('person__full_name')

answered Sep 28, 2021 at 14:25

Cristian Lazo

1333 silver badges6 bronze badges

Collectives™ on Stack Overflow

Django MySQL distinct query for getting multiple values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related