CODEWITHSUNDEEP
pythondjango
Abhishek
Abhishek

Reputation: 3068

Django filter with distinct and values issue

I have the following code:

>>> y = Menu.objects.filter(level=1).values('name').distinct()
>>> y
[{'name': u'Desserts'}, {'name': u'Wings'}, {'name': u'Biriyani'}, {'name': u'Starters'}, {'name': u'Desserts'}, {'name': u'Starters'}, {'name': u'Biriyani'}]

It is not giving me a distinct result set. How can i achieve distinct values?

According to both the solutions, i got the error:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/query.py", line 138, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/query.py", line 162, in __iter__
    self._fetch_all()
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/query.py", line 965, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/query.py", line 1217, in iterator
    for row in compiler.results_iter():
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 794, in results_iter
    results = self.execute_sql(MULTI)
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql
    cursor.execute(sql, params)
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/abhishek.yeruva/workspace/myreps/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("myapp_menu"."name") "myapp_...

Upvotes: 1

Views: 819

Answers (3)

Tom
Tom

Reputation: 22831

See the note here-- if you are using order_by the field you are ordering on will be included in the DISTINCT. You don't show an order_by in the query but if you have one set in the Meta class of your Menu model it will still apply.

Upvotes: 1

Dima Kudosh
Dima Kudosh

Reputation: 7366

Use values_list instead of values and set flat parameter in values_list:

y = set(Menu.objects.filter(level=1).values_list('name', flat=True))

Pass your value list to set function and it create a set with unique values like Joey Wilhelm write in comment.

Upvotes: 2

Joey Wilhelm
Joey Wilhelm

Reputation: 5819

Alternative to Dima's solution, if you are using PostgreSQL, you can specify what field to perform the distinct query on, as described in the .distinct() documentation. For example:

y = Menu.objects.filter(level=1).distinct('name').values('name')

Upvotes: 2

Related Questions