SQLAlchemy Group By With Full Child Objects

In this article, we will explore how to use SQLAlchemy Group By With Full Child Objects in Python. SQLAlchemy provides several techniques to achieve SQLAlchemy Group By with Full Child Objects.

SQLAlchemy Group By With Full Child Objects

In this section, we are making a connection with the database, we are creating the structure of two tables with parent-child relationships. Also, we are inserting data in both tables.

Output

👁 employeeTable1.png

Joinedload Technique

Joinedload technique in SQLAlchemy is used to explicitly instruct the ORM to perform a join query to load related objects along with the primary object. In SQLAlchemy, you can enable joined-load loading using the options() and joinedload() methods.

• First, you define a subquery to calculates the count of employees per department using the session.query() method and specify the necessary columns(Department.id,Department.name) and aggregations(func.count(Employee.emp_id)).

session.query(Department,func.count(Employee.emp_id).label("employee_count"))\
 .join(Department.employees)\
 .group_by(Department)\
 .options(joinedload(Department.employees))\
 .all()

• Then use join(Employess) to sepcify that the query should perform an join between the Departmant table and Employee table.

.join(Department.employees)

• We specify the group_by() clause to group the results by the Department entity.

.group_by(Department)

• Then, we use the options() method on the query object to specify additional query options, such as joinedload loading of relationships.
• We pass the joinedload() method as argument to options().
• Inside the joinedload() method, we specify the relationship Department.employees to load. indicating that we want to load the employee's relationship of the Department model.

.options(joinedload(Department.employees))

• finally, we use all() to retrieve the data.

.all()

Code Implementation

Output

👁 joinedloadOP.png

Contains Eager Loading Technique

contains eager technique in SQLAlchemy is used to indicate that a query should eagerly load a specific relationship and include it in the result set. It is particularly useful when you have loaded a relationship using a separate query or a different loading technique, and you want to include those related objects in the result set of the current query.

• First, you define a subquery to calculates the sum of salaries of employees per department using the session.query() method and specify the necessary columns(Department.id,Department.name) and aggregations(func.sum(Employee.salary)).

query = session.query(Department, func.sum(Employee.salary)) \
 .join(Employee) \
 .group_by(Department.name) \
 .options(contains_eager(Department.employees)) \
 .all()

• Then use join(Employess) to specify that the query should perform an join between the Department table and Employee table.

.join(Employee)

• We specify the group_by() clause to group the results by the Department entity.

.group_by(Department.name) 

• Then, we use the options() method on the query object to specify additional query options, such as contains_eager loading of relationships.
• we pass the contains_eager() method as argument to options().
• Inside the contains_eager() method, we specify the relationship Department.employees to load. indicating that we want to load the employee's relationship of the Department model.

.options(contains_eager(Department.employees)) 

• Finally, we use all() to retrieve the data

.all()

Code Implemention

Output

👁 containsEagerLoad.jpg

Subquery Technique

We first define a subquery that calculates the count of employees per department. The subquery is created using session.query() with the necessary columns and aggregations, followed by a join() with the Employee table and a group_by() on the Department.id column. The subquery() at the end converts the query into a subquery object.

session.query(Department.id, func.count(Employee.emp_id).label('employee_count'))
 .join(Employee)
 .group_by(Department.id)
 .subquery()

• First, you define a subquery to calculates the count of employees per department using the session.query() method and specify the necessary columns(Department.id,Department.name) and aggregations(func.count(Employee.emp_id)).

.join(Employee)

• Then use join(Employess) to specify that the query should perform a join between the Department table and Employee table.

.group_by(Department.id)

• Then we use group_by() method is used to group the results by the id column of the Department.

.subquery() 

• Finally we use .subquery() it will convert the query object to subquery object

Code

Output :

👁 subQueryOP.png

Load Technique

By using the Load technique, you can fine-tune how SQLAlchemy fetches related objects, optimizing performance and reducing the number of queries issued to the database.

query = session.query(Department.name, func.avg(Employee.salary)) 
 .join(Employee, Employee.dep_id == Department.id) 
 .group_by(Department.name) 
 .options(Load(Employee))
 .all()

• session.query(Department.name, func.avg(Employee.salary)): We start by creating a query object that selects the name attribute from the Department model and calculates the average salary using the func.avg() function on the salary attribute from the Employee model.
• join(Employee, Employee.dep_id == Department.id): We use the join() method to join the Employee table with the Department table with joining condition.
• group_by(Department.name): We group the results by the name attribute of the Department model. This ensures that we get the average salary per department.
• options(Load(Employee)): We specify the loading options for the Employee model using the Load() technique. By passing Load(Employee), we indicate that we want to customize the loading behavior for the Employee objects in the query.
• all(): We execute the query and retrieve all the results.

Code

Output

Load technique result
Dep averageSalary
ECE 98333.3333
CSE 55000.0000
MECH 31666.6667

Selectin Load Technique

The selectinload() method allows you to load related objects in a separateSELECTstatement rather than joining them with the main query. This can be useful when you have a large number of related objects or when the relationship involves many-to-many or one-to-many associations.

Output

👁 selectin-Load.png

Immediate Loading Technique

Immediate load is a technique in SQLAlchemy that loads related objects immediately along with the parent object when the query is executed. It allows you to retrieve all the necessary data in a single query, reducing the number of database round-trips. It can be achieved by using the immediateload() in options().

Output

👁 immediateLoad.jpg

Raise Load Technique

The raiseload() method is used to specify that a particular relationship should be loaded using a separate SQL query when accessed.

Output

👁 raiseLoad.png

Noload Technique

The noload technique is used to specify that a particular relationship should not be loaded at all. It is useful when you want to exclude a specific relationship from being loaded in a query, even if it has a default loading strategy.

Output

Dep maxSalary
ECE 120000
CSE 75000
MECH 40000

Lazy Loading Technique

We can simply achieve the same functionality by using lazy loading with the help of foreign key relations no need for joins and group_by clauses.

Output

👁 lazyLoading.jpg

Defaultload Technique

The defaultload technique in SQLAlchemy is used to control the loading behavior of relationships when querying objects from the database. By default, SQLAlchemy loads all related objects for a given relationship when the parent object is queried. However, in some cases, loading all related objects upfront can lead to unnecessary performance overhead.

Output

👁 defaultLoading.jpg