By continuing to navigate on this website, you accept the use of cookies to serve you more relevant services & content.
For more information and to change the setting of cookies on your computer, please read our Cookie Policy.

How to filter a Django Queryset using Extra

SQL Query in Django ORM:

Using Django ORM we can perform all queryset operations. In some cases, we need to use SQL Queries in Django ORM. Here is the scenario if a model having a field with positiveInteger of another model, When I want to get an object of this positive Integer Field we will Query again for that model, By this number of Queries will be increased to reduce this we will use SQL Query to get appropriate results in single Query.

testapp/models.py

class State(models.Model):

    name=models.CharField(max_length=150)

class City(models.Model):

    name=models.CharField(max_length=150)

class Student(models.Model):

    name=models.CharField(max_length=150)

    state_id=models.PositiveIntegerField()

    city_id=models.PositiveIntegerField()

    is_active = models.BooleanField(default=False)

Here Student Model is having state and city as positiveInteger which provides the ID of state model and city model. In this case, when the student details required for every object we should query again to get state name and city name. By using extra({}) in the Django ORM we can filter state and city objects with single query

students = Student.objects.filter(

        is_active=True,

    ).extra(

        select={

            'state':

                'SELECT name FROM state WHERE '

                'state.id = '

                'testapp_student.state_id',

            'city':

                'SELECT name FROM city WHERE '

                'city.id = '

                'testapp_student.city_id',

        },

    )

By Using select in extra({}) provides state object and city object instead of their IDs with a single query.

In the above Query, we are first filtering student objects with active, for each student object executes a select query to filter state object and city object with the provided state_id and city_id.

    Posted On
  • 30 October 2017
  • By
  • Micropyramid

Need any Help in your Project?Let's Talk

Latest Comments
Related Articles
Django webpacker - A compression tool to bundles css, js files

django-webpacker is a django compressor tool which bundles css, js files to a single css, js file with webpack and updates your html files with ...

Continue Reading...
Django Unit Test cases with Forms and Views

By writing unit test cases, you can evaluate each code component in the initial stage itself and it'll improve your app/code performance. Which is the ...

Continue Reading...
Django efficient implementation of Amazon s3 and Cloudfront CDN for faster loading.

Django by default to store the files in your local file system. To make your files load quickly and secure we need to go for ...

Continue Reading...
open source packages

Subscribe To our news letter

Subscribe and Stay Updated about our Webinars, news and articles on Django, Python, Machine Learning, Amazon Web Services, DevOps, Salesforce, ReactJS, AngularJS, React Native.
* We don't provide your email contact details to any third parties