Advanced Queries in SQLAlchemy (Filters, Joins, Aggregations, Subqueries)

 

Advanced Queries in SQLAlchemy (Filters, Joins, Aggregations, Subqueries)

Introduction

Once you know basic CRUD, the next step is writing powerful, real-world database queries.

In this blog, you’ll learn:

  • Advanced filtering
  • Joins (INNER, LEFT)
  • Aggregations (COUNT, SUM, AVG)
  • Subqueries & nested queries
  • Performance tips

Sample Models (Setup)

We’ll use two tables:

from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class User(Base):

    __tablename__ = "users"

    id = Column(Integer, primary_key=True)

    name = Column(String)

    orders = relationship("Order", back_populates="user")


class Order(Base):

    __tablename__ = "orders"

    id = Column(Integer, primary_key=True)

    amount = Column(Integer)

    user_id = Column(Integer, ForeignKey("users.id"))

    user = relationship("User

1. Advanced Filtering

Basic Filter

session.query(User).filter(User.name == "John").all()

Multiple Conditions

from sqlalchemy import and_, or_

# AND condition
session.query(User).filter(
    and_(User.name == "John", User.id > 1)
).all()

# OR condition
session.query(User).filter(
    or_(User.name == "John", User.name == "Alice")
).all()

LIKE (Search)

session.query(User).filter(User.name.like("%Jo%")).all()

IN Operator

session.query(User).filter(User.id.in_([1, 2, 3])).all()

NOT / !=

session.query(User).filter(User.name != "John").all()

", back_populates="orders")


2. Joins

What is a Join?

A join combines data from multiple tables.

INNER JOIN

session.query(User).join(Order).all()

Returns users who have orders

Fetch User + Order Data

results = session.query(User, Order).join(Order).all()

for user, order in results:
    print(user.name, order.amount)

LEFT JOIN

session.query(User).outerjoin(Order).all()

Returns all users (even those without orders)

Real-World Example

Get users who placed orders above ₹500:

session.query(User).join(Order).filter(Order.amount > 500).all()


3. Aggregations (COUNT, SUM, AVG)
from sqlalchemy import func
COUNT
session.query(func.count(User.id)).scalar()
SUM
session.query(func.sum(Order.amount)).scalar()
AVG
session.query(func.avg(Order.amount)).scalar()
GROUP BY
session.query(
    Order.user_id,
    func.sum(Order.amount)
).group_by(Order.user_id).all()
Total order amount per user
HAVING
session.query(
    Order.user_id,
    func.sum(Order.amount).label("total")
).group_by(Order.user_id).having(func.sum(Order.amount) > 1000).all()
Users with total orders > ₹1000
4. Subqueries
What is a Subquery?
A query inside another query.
Example: Users with High Orders
subquery = session.query(Order.user_id).filter(Order.amount > 500).subquery()

session.query(User).filter(User.id.in_(subquery)).all()
Using Alias
from sqlalchemy.orm import aliased

OrderAlias = aliased(Order)

session.query(User).join(OrderAlias).all()
5. Lazy vs Eager Loading
Problem (N+1 Query Issue)
users = session.query(User).all()

for user in users:
    print(user.orders)  # multiple queries!
Solution: Eager Loading
from sqlalchemy.orm import joinedload

users = session.query(User).options(joinedload(User.orders)).all()
Loads everything in one query
6. Ordering & Limiting Results
ORDER BY
session.query(User).order_by(User.name).all()
DESC Order
from sqlalchemy import desc

session.query(User).order_by(desc(User.id)).all()
LIMIT
session.query(User).limit(5).all()
OFFSET (Pagination)
session.query(User).offset(5).limit(5).all()
Real-World Use Case
E-commerce Example :Top 5 users who spent the most:
session.query(
    User.name,
    func.sum(Order.amount).label("total")
).join(Order).group_by(User.id)\
 .order_by(desc("total"))\
 .limit(5).all()
Common Mistakes
  • Using .all() when .first() is enough

  • Not using indexes in DB

  • Ignoring N+1 query problem

  • Writing inefficient joins

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more