2

Using mysql for backend DB. Querying all records from Database like:

ob = Shop.objects.all()

Here querying all records because need all the n number of columns values. Then come to the Question.

Want to get all the non duplicate records based on a certain column. Tried with Python SET function. But it deletes exact same records. I have records which are having same value in a particular column value but not same value in other columns.

Can some one share some idea hoe to do this with the help of Django ORM!!!

danny
  • 983
  • 1
  • 7
  • 13
  • Do you mean you want "distinct" values?... – Sayse Apr 19 '16 at 07:25
  • yes. I tried using distinct..but mysql doesn't support distinct in django orm. – danny Apr 19 '16 at 07:26
  • Possible duplicate of [Select DISTINCT individual columns in django?](http://stackoverflow.com/questions/3852104/select-distinct-individual-columns-in-django) – Sayse Apr 19 '16 at 07:30
  • my database doesn't support DISTINCT function. I cant change any other database..Rigth now using Mysql.....Any idea??? – danny Apr 19 '16 at 07:33
  • That answer was for mysql, you can use `distinct` on mysql (as far as I can tell) but only postgres supports `distinct(fields)` – Sayse Apr 19 '16 at 07:34
  • Using `distinct` with `values` will only return the field using in `values` and not the `shop` objects. – AKS Apr 19 '16 at 07:36
  • I want all the distinct records with all columns values? Will the above work for me? – danny Apr 19 '16 at 08:50
  • @Sayse Your answer is not working for me...Want all distinct records with all column values – danny Apr 19 '16 at 08:53
  • @AKS you are right.. it will return the field using values....But I want all shop object...Any idea still in confusion – danny Apr 20 '16 at 04:25

1 Answers1

1

If I have understood your question correctly you only want to retrieve the records that do not have duplicates. You can do so with this query:

SELECT * from shops GROUP by (duplicate_field_name) having COUNT(*) = 1

How to do this with django? Well GROUP by is what sucks the most about Django. However this can be achieved with a raw query.

ob = Shop.objects.raw('SELECT * from shops GROUP BY
 (duplicate_field_name) having COUNT(*) = 1 ORDER BY some_field')

remember to replace shops, and duplicate_field_name with the actual name of the table and column name respectively.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • I am getting this error "RawQuerySet' object has no attribute 'order_by" when applying order_by.What should i do? – danny Apr 19 '16 at 16:18
  • you understood the Q clearly.But still now I did't get the answer. Any help i can expect? – danny Apr 19 '16 at 16:30
  • Yes, you can't use the order_by() call with raw querysets. What you can do is add the ordering to the query itself. Updating answer. – e4c5 Apr 20 '16 at 00:38
  • i can't add the ordering with the query itself..Because I need these records in some context & then only i can apply ordering..Before my functionality will break – danny Apr 20 '16 at 04:11
  • one more thing i am passing a list for ordering..Then how can i proceed? – danny Apr 20 '16 at 04:17
  • What exactly do you mean passing a list for ordering? What I have answered is your original question which does not mention anything about ordering. That question was how to query Shop objects excluding duplicates. – e4c5 Apr 20 '16 at 09:24
  • Your answer is correct for some extend if I am passing as ordering inside raw sql query...But i am using like abc = ob[*slist]. Wher slist =['check-in',check-out].....like many more i am passing inside the list.There i am confused how to pass the ordering list inside a raw sql query – danny Apr 20 '16 at 09:44
  • That is an entirely different question all together. My answer is correct for the question you have asked. Please mark this as correct and ask another one. Mention this list and list ordering clearly. – e4c5 Apr 20 '16 at 10:25
  • Ok.I am marking as correct answer. But answer how to apply ordering in raw query sets...Its relative to this question – danny Apr 20 '16 at 11:12
  • But that is already there in my answer " ... ORDER BY some_field" you then started talking about some more complex ordering. That's why I asked you to post another question. – e4c5 Apr 20 '16 at 11:36
  • I'm trying to avoid using raw queries in my project, but I wasn't able to find any way to replace GROUP BY this specific case. So, well, thank you! – Luca D'Amico Jul 26 '19 at 12:24