Practical Multi-Table Queries for FastAPI

Knowing JOIN syntax is one thing; writing efficient, real-world multi-table queries is another. This lesson applies joins, subqueries, and aggregates to the exact scenarios you will encounter building FastAPI endpoints: fetching a post with all its related data in one query, generating paginated lists with metadata, avoiding the N+1 query problem that degrades performance, and writing the kinds of queries that power admin dashboards and search features. These patterns are what you will translate directly into SQLAlchemy ORM calls when Part 3 of this series begins.

Fetching a Resource with All Related Data #

-- GET /posts/{post_id} — return post with author and tags in one query

SELECT
    p.id                                        AS post_id,
    p.public_id,
    p.title,
    p.body,
    p.status,
    p.view_count,
    p.created_at,
    p.published_at,
    -- Author (inline, not nested)
    u.id                                        AS author_id,
    u.name                                      AS author_name,
    u.email                                     AS author_email,
    -- Tags (aggregated into an array)
    COALESCE(
        ARRAY_AGG(t.name ORDER BY t.name) FILTER (WHERE t.id IS NOT NULL),
        '{}'
    )                                           AS tags,
    -- Comment count
    COUNT(DISTINCT c.id)                        AS comment_count
FROM posts p
JOIN  users u     ON p.author_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t       ON pt.tag_id = t.id
LEFT JOIN comments c   ON p.id = c.post_id AND c.is_approved = TRUE
WHERE p.id = :post_id
GROUP BY p.id, u.id, u.name, u.email;

Paginated List with Metadata #

-- GET /posts?page=2&limit=10&status=published
-- Returns: items + total count for pagination UI

-- Query 1: total count (run first to check pagination validity)
SELECT COUNT(*) AS total
FROM posts
WHERE status = 'published'
  AND deleted_at IS NULL;

-- Query 2: paginated items with related data
SELECT
    p.id,
    p.title,
    p.slug,
    p.status,
    p.view_count,
    p.created_at,
    u.name  AS author_name,
    COUNT(DISTINCT c.id) AS comment_count,
    COALESCE(
        ARRAY_AGG(DISTINCT t.name) FILTER (WHERE t.id IS NOT NULL),
        '{}'
    ) AS tags
FROM posts p
JOIN  users u     ON p.author_id = u.id
LEFT JOIN comments c   ON p.id = c.post_id AND c.is_approved = TRUE
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t       ON pt.tag_id = t.id
WHERE p.status = 'published'
  AND p.deleted_at IS NULL
GROUP BY p.id, p.title, p.slug, p.status, p.view_count, p.created_at, u.name
ORDER BY p.created_at DESC, p.id DESC
LIMIT 10 OFFSET 10;   -- page 2 = OFFSET (page-1)*limit = 10

Avoiding N+1 — Batch Loading Related Data #

-- N+1 PROBLEM:
-- Step 1: Fetch 20 posts
SELECT id, title, author_id FROM posts WHERE status = 'published' LIMIT 20;
-- Step 2: For each post, fetch author (20 separate queries!)
SELECT name FROM users WHERE id = 1;
SELECT name FROM users WHERE id = 1;  -- same user, 5 times!
SELECT name FROM users WHERE id = 2;
-- ... 20 total user queries → 21 queries total

-- SOLUTION 1: JOIN (embed author in the main query)
SELECT p.id, p.title, u.name AS author_name
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
LIMIT 20;
-- 1 query instead of 21 ✓

-- SOLUTION 2: Two efficient queries (useful for one-to-many relationships)
-- Step 1: Get the posts
SELECT id, title, author_id FROM posts WHERE status = 'published' LIMIT 20;
-- Step 2: Get ALL relevant authors in ONE query using IN
SELECT id, name FROM users WHERE id IN (1, 2, 3, ...);
-- 2 queries instead of 21 ✓ — and no duplicate user fetching

-- SOLUTION 3: SQLAlchemy selectinload / joinedload (covered in Part 3)
-- These ORM options generate the efficient two-query approach automatically

Dashboard Aggregation Query #

-- Admin dashboard: per-author statistics
-- GET /admin/stats/authors

SELECT
    u.id                                        AS author_id,
    u.name                                      AS author_name,
    u.email,
    COUNT(p.id)                                 AS total_posts,
    COUNT(p.id) FILTER (WHERE p.status = 'published') AS published_posts,
    COALESCE(SUM(p.view_count), 0)              AS total_views,
    ROUND(AVG(p.view_count) FILTER (WHERE p.status = 'published'), 0)
                                                AS avg_views_published,
    MAX(p.created_at)                           AS last_post_date,
    COUNT(DISTINCT c.id) FILTER (WHERE c.is_approved = TRUE)
                                                AS comments_received
FROM users u
LEFT JOIN posts p    ON u.id = p.author_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE u.is_active = TRUE
GROUP BY u.id, u.name, u.email
HAVING COUNT(p.id) > 0   -- only users who have posted
ORDER BY total_views DESC
LIMIT 20;

Common Mistakes #

Mistake 1 — The N+1 query problem #

❌ Wrong — one query per post for its author:

posts = db.query(Post).limit(20).all()
for post in posts:
    author = db.query(User).get(post.author_id)   # 20 extra queries!

✅ Correct — JOIN in the ORM or use selectinload:

posts = db.query(Post).options(joinedload(Post.author)).limit(20).all()
# Or: use selectinload for one-to-many relationships

Mistake 2 — Missing GROUP BY columns causing aggregate errors #

❌ Wrong — non-aggregated columns not in GROUP BY:

SELECT p.id, p.title, u.name, COUNT(c.id)
FROM posts p JOIN users u ... LEFT JOIN comments c ...
GROUP BY p.id;   -- ERROR: u.name not in GROUP BY and not aggregated!

✅ Correct — include all non-aggregated SELECT columns in GROUP BY:

GROUP BY p.id, p.title, u.id, u.name   -- ✓

Mistake 3 — Forgetting FILTER on ARRAY_AGG with LEFT JOINs #

❌ Wrong — post with no tags returns {NULL}:

ARRAY_AGG(t.name)   -- returns {NULL} when no tags match

✅ Correct:

COALESCE(ARRAY_AGG(t.name) FILTER (WHERE t.id IS NOT NULL), '{}')   -- ✓ empty array

Quick Reference — Multi-Table Query Checklist #