CODEWITHSUNDEEP
djangodjango-querysetdjango-database
candito123
candito123

Reputation: 65

Django Queryset filter equivalency to SQL

To me, it is not clear or lack of example code to understand the documentation about how SQL language is internally used in Django ORM.

My intended SQL is:

SELECT projects_category.id, projects_category.name, projects_project.name
FROM projects_category
    JOIN projects_project_categories
        ON projects_category.id = projects_project_categories.project_id
    JOIN projects_project
        ON projects_project_categories.project_id=projects_project.id
            WHERE NOT projects_project.is_mini ORDER BY projects_category.id;

How to do the same in Django ORM or in Django view? I have tried filter and exclude (probably with the wrong arguments...) and it does not seem working according to output. There might be more options besides these two.

Also, any other trick would be appreciable.

My relevant models below:

class Project(models.Model):
    name = models.CharField(max_length=100)
    is_mini = models.BooleanField(default=False)
    categories = models.ManyToManyField('Category', blank=True)

    def __str__(self):
        return self.name

class Category(models.Model): # will be used for search
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

I want to get the queryset that contains category sets that contains project sets that are not mini

Upvotes: 0

Views: 140

Answers (2)

fannik
fannik

Reputation: 1153

Several examples with different performance:

General QuerySet

Project.objects.filter(is_mini=False).order_by('categories__id')

Only needed values

Project.objects.only("name", "categories__name", "categories__id").filter(
    is_mini=False
).order_by('categories__id')

Prefetch categories + values

You can combine this answer with @Midoki's one and use values instead of only or even values_list. Doc is here

Project.objects.values("name", "categories__name", "categories__id").filter(
    is_mini=False
).prefetch_related("categories").order_by("categories__id")

When you are using the ManyToMany field (and if there are many rows here) you can let Django ORM prefetch categories for performance. Docs is here

Upvotes: 1

Norbox
Norbox

Reputation: 127

To do this request I propose to do it in the following way:

Project.objects.values('name','categories__name','categories__id').filter(is_mini=False).order_by('id')

.values allows you to choose the columns you want to select from the Project object. And as it has a ManyToMany relation with "category" you can access the data of these categories with the keyword 'categories__field'.

Upvotes: 2

Related Questions