Django model filter and do distinct by specific column

Question

I have two models

class Category(models.Model):
    name= models.TextField(max_length=255)
    parent = models.ForeignKey('self', null=True, blank=True)

class Brand(models.Model):
    category = models.ForeignKey(Category)
    name= models.TextField(max_length=255)

For example:

Category

         name        parent
         -------      -------
         vehicle        0
         car            1 
         motorcycle     1 
         truck          1 
         bicycle        1
         fff            0
         ....

Brand

            name      category
            ----      --------- 
            BMW        car
            BMW        truck
            BMW        bicycle
            toyota     car
            mercedes   car
            mercedes   truck
            someThing  fff
            ....

I want to create a queryset of Brand that is filtered by vehicle and to be distinct by name.

so I can create a form in my template that will have a drop down filter with all the brands related to vehicle categories with no duplicate of names

            name     category
            ----     --------- 
            BMW       car
            toyota    car
            mercedes  truck

Is there any option to do it in a simple way,or do I need to write a function for that?

I saw an example
Select DISTINCT individual columns in django? but it returns a ValuesQuerySet and I need a QuerySet, and I prefer not to use ().distinct('someItem') that is supported only on PostgreSQL.

Matt Hardcastle · Answer 1 · 2017-09-13T13:45:50.747

0

The simplest way to avoid duplicates in your QuerySet is to add a unique constraint on the name field for both your Brand and Category models. This will stop the duplicates from being inserted in the first place.

The constraint will cause a IntegrityError to be thrown any time you attempt to create a Brand or Category who's name already exists in the database. You can avoid the error by using ModelName.objects.get_or_create instead of ModelName.objects.create().

edited Sep 13 '17 at 13:45

answered Sep 13 '17 at 13:37

Matt Hardcastle

638
3
9

Its OK to have the same brand for different categories in the DB, but if I want to filter by brand the user doesn't need to see in drop down duplicate names – Ron Sep 13 '17 at 13:45
This joins brand and category, which can cause duplicates. A distinct will remove them at the cost of being slightly exspensive. Category.objects.filter(brand__name__in=("BMW", "toyota")).distinct() You can avoid the join by looking up the brands by pk and passing that in: brands = Brand.objects.filter(name__in=("BMW", "toyota")) Category.objects.filter(brand_id__in=brands) – Matt Hardcastle Sep 13 '17 at 13:57
I need to join brand to category as they are related. The user can add items to the system and when he do so he must choose only brands that are related to the category, so I display him the relevant brands related to the category he is in, And he can filter the items by brand , category , or category and related brand – Ron Sep 13 '17 at 14:11

Django model filter and do distinct by specific column

1 Answers1