We will be using the following model for this blog:

class Employee(models.Model):
    # table name = employee_employee
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50, default="")
    designation = models.CharField(max_length=50,default="")
    dob = models.DateField(blank=True, null=True) 
    age = models.CharField(max_length=50,default="")
    gender = models.CharField(max_length=50,default="")
    marital_status = models.CharField(max_length=50, default="")
    address = models.CharField(max_length=200, default="")
    email_address = models.CharField(max_length=50,default="")
    joined_date = models.DateField(blank=True, null=True)
  
class Salary(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    salary_amount = models.IntegerField()

Filter QuerySet:

A Django Filter QuerySet method is used to retrieve data from a database model, specified conditions, or criteria using lookup parameters.

It returns a QuerySet, which can be further evaluated.

Scenario 1 (single where condition):

Imagine an HR needs to fetch all employees who are Female.

Query:

Employee.objects.filter(gender="Female")

The above query selects all records in the Employee model where the gender is "Female." Here, the condition is passed as the lookup parameter, where "gender" is the field name, and "Female" is the value.

Equivalent SQL Query:

SELECT * FROM employee_employee WHERE gender = "Female";

You can pass as many conditions as needed. In the following Filter QuerySet example, we'll be passing two conditions where the gender is "Female" and the designation is "Trainee."

Scenario 2 (multiple where conditions):

Imagine an HR needs to fetch all employees who are Female, and their position is "Trainee".

Query:

Employee.objects.filter(gender="Female", designation="Trainee")

Equivalent SQL Query:

The equivalent SQL query would look like this:

SELECT * FROM employee_employee WHERE gender = "Female" AND designation = "Trainee";

Values QuerySet:

The values() method allows you to select specific fields from your database model and return them as a QuerySet of dictionaries, making it easy to work with structured data.

It returns a QuerySet, which can be further evaluated.

Scenario 1 (Fetch all fields):

Imagine you're managing a database of employees, and you want to retrieve a list of employee names and their respective designations for further analysis. This is where the values() method becomes invaluable.

Query:

Here's how you can use the values() method to retrieve a list of employee names and their respective designations:

employee_data = Employee.objects.values()

In this query, we use values() to select the name and designation fields from the Employee model.

Equivalent SQL Query:

To provide insight into how Django's values() method translates into SQL, here's the equivalent SQL query for the above Django query:

SELECT * FROM employee_employee;

Scenario 1 (select particular fields):

Imagine you're managing a database of employees, and you want to retrieve all available information about each employee for a comprehensive analysis. This is where the values() method without field names comes into play.

Query:

employee_data = Employee.objects.values('name', 'designation')

In this query, we use values() without specifying any field names, which results in the retrieval of all fields from the Employee model.

Equivalent SQL Query:

To provide insight into how Django's values() method without field names translates into SQL, here's the equivalent SQL query for the above Django query:

SELECT * FROM employee_employee;

Values List QuerySet:

The values_list() method allows you to select specific fields from your database model and return them as a structured list or tuple, providing a convenient format for further processing.

Note: Add scenario for both flat true and default values_list

Scenario:

Imagine you're managing a database of employees, and you want to retrieve a list of employee names for a report or analysis. This is where the values_list() method becomes invaluable.

Query:

employee_names = Employee.objects.values_list('name', flat=True)

In this query, we use values_list() with the field name 'name' and flat=True to obtain a flat list of employee names.

Equivalent SQL Query:

SELECT name FROM employee_employee;

This SQL query retrieves the name column (field) from the employee_employee table.

Exclude QuerySet:

A Django Exclude QuerySet method is used to retrieve data from a database model while excluding specific records that meet certain criteria.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine an HR needs to fetch all employees except those who are “Married“. (a simple filter is enough for this but for a simple explanation)

Query:

Here's how you can use the exclude() method to achieve our scenario:

Employee.objects.exclude(marital_status="Married")

In this query, we're selecting all records from the Employee model but excluding those where the marital_status is "Married."

Equivalent SQL Query:

The equivalent SQL query would look like this:

SELECT * FROM employee_employee WHERE marital_status != "Married";

You can pass as many conditions as needed. In the following Filter QuerySet example, we'll be passing two conditions where the marital_status is “Married” and gender is "Male" and the designation is "Trainee."

Scenario:

Imagine an HR needs to fetch all employees who are “Married and “Male”.

Query:

Employee.objects.exclude(marital_status="Married", gender="Male")

Equivalent SQL Query:

The equivalent SQL query would look like this:

SELECT COUNT(*) FROM employee_employee WHERE marital_status != "Married" AND gender = "Male";

Annotate QuerySet:

Annotates each object in the QuerySet with the provided list of query expressions. An expression may be a simple value, a reference to a field on the model (or any related models), or an aggregate expression (averages, sums, etc.) that has been computed over the objects that are related to the objects in the QuerySet.

Simply it lets us perform calculations, such as finding averages, totals, or counts on our data.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees, and you want to calculate the duration each employee has spent in the company. This is where we would use the annotate() method.

Query:

Here's how you can use the annotate() method to calculate the time completed by employees:

from django.db.models import F, ExpressionWrapper, fields
from django.utils.timezone import now

employee_data = Employee.objects.annotate(
    time_completed=ExpressionWrapper(
        now().date() - F('joined_date'),
        output_field=fields.DurationField()
    )
)
print(employee_data.values())

In this query, we use annotate() to add a field called time_completed, which represents the duration each employee has spent in the company. We calculate this duration using ExpressionWrapper to subtract the joined_date of each employee from the current date.

ExpressionWrapper allows you to perform complex calculations within your queries. It's particularly useful when you need to manipulate data before adding it to your query results.

Order By QuerySet:

Sorts the data in ascending or descending order based on one or more fields.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees, and you want to retrieve a list of employees sorted by their names in alphabetical order. This is where the order_by() method becomes invaluable.

Note: add scenario 2 for descending

Query:

Here's how you can use the order_by() method to retrieve a list of employees sorted by their names in alphabetical order:

employees_sorted_by_name = Employee.objects.order_by('name')

In this query, we use the order_by() method with the field 'name' to sort the employees in ascending alphabetical order by their names.

Equivalent SQL Query:

To provide more insight into how Django's order_by() method translates into SQL, here's the equivalent SQL query for the above Django query:

SELECT * FROM employee_employee ORDER BY name ASC;

This SQL query retrieves all records from the employee_employee table and sorts them in ascending order based on the name column.

Sorting in Descending Order:

If you want to retrieve the list of employees in descending order by their names, you can use the - symbol with the field name:

employees_sorted_by_name_desc = Employee.objects.order_by('-name')

Now, the employees_sorted_by_name_desc QuerySet will contain the list of employees sorted by their names in descending order.

Distinct QuerySet:

The distinct() method is like a filter that returns only the unique values from a specific field by eliminating the duplicate rows from the query results.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees, and you want to retrieve a list of distinct job designations present in the database. This is where the distinct() method becomes invaluable.

Query:

Retrieve a list of distinct job designations from the database using the distinct() method:

distinct_desigations = Employee.objects.values_list('designation', flat=True).distinct()

In this query, we use values_list() to extract the values of the designation field and then apply distinct() to obtain a list of unique job designations.

Equivalent SQL Query:

To provide insight into how Django's distinct() method translates into SQL, here's the equivalent SQL query for the above Django query:

SELECT DISTINCT designation FROM employee_employee;

This SQL query retrieves all distinct values from the designation column in the employee_employee table.

All QuerySet:

The all() method allows you to obtain all records from a specified database model. It retrieves the complete dataset, providing a starting point for various data-related tasks.

When a QuerySet is evaluated, it typically caches its results. If the data in the database might have changed since a QuerySet was evaluated, you can get updated results for the same query by calling all() on a previously evaluated QuerySet.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a comprehensive database of employees, and you want to retrieve all available employee records to perform tasks like data validation, data export, or statistical analysis. This is where the all() method becomes invaluable.

Query:

all_employees = Employee.objects.all()

In this query, we use all() to retrieve all records from the Employee model.

Equivalent SQL Query:

SELECT * FROM employee_employee;

This SQL query retrieves all columns (fields) and records from the employee_employee table.

Union QuerySet:

The union() method enables you to combine multiple QuerySet. It allows you to treat data from different sources as a single, unified dataset.

The Union Operation can be applied on the same or different models. The fields and data types should match when QuerySets are from distinct models or even from the same model.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees and their salaries, and you want to retrieve a list of employees who are either interns or have a salary greater than 50,000. These two conditions result in a separate QuerySet, but you need to combine them into one cohesive list of employees. This is where the union() method becomes invaluable.

Query:

from django.db.models import Q

interns = Employee.objects.filter(designation="Intern")
high_salary_employees = Employee.objects.filter(salary__salary_amount__gt=50000)

combined_employees = interns.union(high_salary_employees, all=True)

In this query, we use the union() method to combine the interns and high_salary_employees QuerySet into a single combined_employees QuerySet. We navigate the relationship between Employee and Salary using the salary foreign key.

Equivalent SQL Query:

❌ The below SQL is not right

SELECT id, name, designation, joined_date
FROM employee_employee
WHERE designation = 'Intern'
UNION ALL
SELECT E.id, E.name, E.designation, E.joined_date
FROM employee_employee AS E
INNER JOIN salary_salary AS S ON E.id = S.employee_id
WHERE S.salary_amount > 50000;

Intersection QuerySet:

The intersection() method allows you to find common elements between two or more QuerySets.

Scenario:

Imagine you're developing a Human Resources management system in Django, and you want to identify common skills among employees. This information can help in making informed decisions about training programs, project assignments, and team collaboration.

Query:

evelyn = Employee.objects.get(name="Evelyn Little")
harrison = Employee.objects.get(name="Harrison Shannon")

evelyn_skills = evelyn.skills.values_list('skill', flat=True)
harrison_skills = harrison.skills.values_list('skill', flat=True)

common_skills = set(evelyn_skills).intersection(harrison_skills)

for skill in common_skills:
    print(skill)

In this query, we first retrieve the skill values for Evelyn and Harrison using the values_list() method, and then we use the intersection() method to find common skills. The result is a set of shared skills that we can iterate through and display.

Equivalent SQL Query:

SELECT "employee_skill"."skill" AS "common skill" FROM "employee_skill" WHERE "employee_skill"."employee_id" = 11
INTERSECT
SELECT "employee_skill"."skill" AS "common skill" FROM "employee_skill" WHERE"employee_skill"."employee_id" = 4;

Difference QuerySet:

The difference() method allows you to find elements that are unique to one QuerySet compared to another.

Scenario:

Imagine you're managing a team of employees in a software development company, and you want to identify the skills that are unique to each team member. By doing so, you can better allocate project tasks and promote knowledge sharing.

Query:

evelyn = Employee.objects.get(name="Evelyn Little")
harrison = Employee.objects.get(name="Harrison Shannon")

evelyn_skills = evelyn.skills.values_list('skill', flat=True)
harrison_skills = harrison.skills.values_list('skill', flat=True)

unique_skills_evelyn = set(evelyn_skills).difference(harrison_skills)
unique_skills_harrison = set(harrison_skills).difference(evelyn_skills)

print(f"Unique Skills for Alice: {unique_skills_evelyn}")
print(f"Unique Skills for Bob: {unique_skills_harrison}")

In this query, we first retrieve the skill values for Evelyn and Harrison using the values_list() method. Then, we use the difference() method to find unique skills for each employee. The result is two sets, unique_skills_evelyn, and unique_skills_harrison, containing skills unique to each employee.

Equivalent SQL Query:

SELECT "employee_skill"."skill" AS "unique skill" FROM "employee_skill" WHERE "employee_skill"."employee_id" = 11
EXCEPT
SELECT "employee_skill"."skill" AS "unique skill" FROM "employee_skill" WHERE "employee_skill"."employee_id" = 4;

Select Related QuerySet:

The select_related() method retrieves the related objects in the same query as the parent object efficiently by performing an SQL join operation at the database level.

This can improve performance by reducing the number of database queries that need to be executed.

It returns a QuerySet, which can be further evaluated.

To use select_related, you simply need to pass the names of the related objects that you want to retrieve as arguments to the select_related() method. For example, the following query will retrieve all of the Profile objects, along with their associated Skill objects:

Scenario:

Imagine you're managing a database of employees and their associated projects. When displaying a list of projects, you also want to show the respective employee names without causing a performance bottleneck. This is where the select_related() method becomes invaluable.

Query:

projects_with_employees = Project.objects.select_related('employee')

In this query, we use select_related('employee') to fetch projects and their associated employees in a single query.

Equivalent SQL Query:

SELECT 
"employee_project"."id", "employee_project"."name", "employee_project"."employee_id",
"employee_employee"."id", "employee_employee"."name", "employee_employee"."designation",
"employee_employee"."dob", "employee_employee"."age", "employee_employee"."gender",
"employee_employee"."marital_status", "employee_employee"."address",
"employee_employee"."email_address", "employee_employee"."joined_date"
FROM "employee_project" INNER JOIN "employee_employee" ON
("employee_project"."employee_id" = "employee_employee"."id");

Extra QuerySet:

The extra() method allows you to inject custom SQL expressions into your query, enabling you to perform complex database operations that go beyond the typical ORM capabilities.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees and their salaries. You want to retrieve a list of employees along with an additional calculated field, such as the total earnings, which isn't directly available in your models. This is where the extra() method comes to the rescue.

Query:

employees_with_earnings = Employee.objects.filter(id=1).extra(
    select={'total_earnings': 'SELECT SUM(salary_amount) FROM employee_salary WHERE employee_salary.employee_id = employee_employee.id'}
)

In this query, we use extra() to include a custom SQL expression that calculates the total earnings for a specific employee (in this case, an employee with id=1) by summing their salaries from the employee_salary table.

Equivalent SQL Query:

SELECT employee.id, employee.name, 
       (SELECT SUM(salary_amount) FROM employee_salary WHERE employee_salary.employee_id = employee.id) AS total_earnings
FROM employee_employee AS employee
WHERE employee.id = 1;
SELECT (SELECT SUM(salary_amount) FROM employee_salary WHERE employee_salary.employee_id = employee_employee.id) AS "total_earnings", "employee_employee"."id", "employee_employee"."name", "employee_employee"."designation", "employee_employee"."dob", "employee_employee"."age", "employee_employee"."gender", "employee_employee"."marital_status", "employee_employee"."address", "employee_employee"."email_address", "employee_employee"."joined_date" FROM "employee_employee" WHERE "employee_employee"."id" = 1;

This SQL query calculates the total earnings for the employee with id=1 by summing their salaries, just like our Django query.

Defer QuerySet:

The defer() method allows you to specify which fields of a model should be loaded lazily, reducing the initial data overhead when fetching instances of that model.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees with extensive information, including personal details, addresses, and salary history. When displaying a list of employees, you want to retrieve only their basic information initially to improve query performance. This is where the defer() method becomes invaluable.

Query:

employee_info = Employee.objects.defer('designation', 'age', 'gender', 'address', 'email_address')

In this query, we use defer() to specify that fields like designation, age, and others should not be loaded immediately when fetching Employee instances.

Only QuerySet:

The only() method allows you to specify which fields of a model should be loaded, reducing query overhead and enhancing query performance.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees with extensive details, including names, designations, and dates of birth. When displaying a list of employees, you want to retrieve only the employee names and designations initially to reduce query overhead. This is where the only() method becomes invaluable.

Query:

employee_info = Employee.objects.only('name', 'designation')

In this query, we use only() to specify that only the fields 'name' and 'designation' should be loaded initially when fetching Employee instances.

Equivalent SQL Query:

SELECT
"employee_employee"."id", "employee_employee"."name", "employee_employee"."designation"
FROM "employee_employee";

Here, only the fields "id", "name", and "designation" are initially loaded, while the fields not specified in only() are excluded.

Select for Update QuerySet:

The select_for_update() method allows you to lock specific records during a database transaction, preventing concurrent access and modifications

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're managing a database of employees, and you want to ensure that employee records are updated accurately, even if multiple HR managers attempt to modify the same employee's information simultaneously. This is where the select_for_update() method becomes invaluable.

Query:

employee = Employee.objects.select_for_update().get(id=1)
employee.gender = "Female"
employee.save()

In this query, we use select_for_update() to lock the employee record with id=1 for modification. We then update the record of the employee. This ensures that even if multiple HR managers try to update the same employee's salary simultaneously, the changes are processed correctly.

Note: Use the select_for_update() query with transaction.atomic(), as it ensures that the database transaction is performed atomically, which means either all changes within the block are committed to the database, or none of them are. This is crucial for maintaining data integrity and consistency in your database.

Raw QuerySet:

The raw() method allows you to execute custom SQL queries against your database and retrieve the results as model instances or dictionaries.

It returns a QuerySet, which can be further evaluated.

Scenario:

Imagine you're working on a Django project that requires intricate database operations, such as aggregating data across multiple tables, generating custom reports, or executing complex joins. In these scenarios, the raw() method becomes your go-to choose.

Query:

results = Employee.objects.raw("Select * from employee_employee where id =1")
for result in results:
    print(result.name, result.designation)

In this query, we use the Employee.objects.raw() method to execute the raw SQL query Select * from employee_employee where id =1. The results are returned as model instances, allowing you to work with the data as if it were fetched through Django's ORM.

Django QuerySet - Returns QuerySet