Как простроить сложный запрос без ORM?

Как построить сложный запрос без ORM?

Это вопрос, который скорее относится к backend, но frontend разработчику полезно знать, как бэкенд обрабатывает сложные запросы. На фронтенде это не прямо применяется, но понимание SQL помогает правильно писать API запросы и понимать bottleneck.

Что такое ORM?

ORM (Object-Relational Mapping) — это библиотека, которая преобразует объекты в SQL запросы (SQLAlchemy, Django ORM, TypeORM и т.д.).

# С ORM (SQLAlchemy)
users = session.query(User).filter(User.age > 18).join(Profile).filter(Profile.verified == True).all()

# Без ORM — сырой SQL
users = db.execute("""SELECT users.* FROM users JOIN profiles ON users.id = profiles.user_id WHERE users.age > 18 AND profiles.verified = true""")

Почему без ORM?

Когда ORM не подходит:

# Проблема 1: N+1 queries
# ORM может неправильно выполнить JOIN и вызвать много запросов
for user in users:  # SELECT * FROM users (1 запрос)
    print(user.posts)  # SELECT * FROM posts WHERE user_id = ? (N запросов)
# Итого: 1 + N запросов (плохо)

# Проблема 2: Сложная бизнес-логика
# Не всё можно выразить через ORM
users_with_stats = db.execute("""
    SELECT users.*, 
           COUNT(posts.id) as post_count,
           AVG(posts.likes) as avg_likes
    FROM users
    LEFT JOIN posts ON users.id = posts.user_id
    GROUP BY users.id
    HAVING COUNT(posts.id) > 5
""")

# Проблема 3: Производительность
# Сырой SQL может быть в 10x быстрее ORM

Основы SQL для сложных запросов

1. JOINs — связывание таблиц:

-- INNER JOIN — только совпадающие записи
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.age > 18;

-- LEFT JOIN — все из левой таблицы + совпадающие из правой
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- MULTIPLE JOINs — несколько связей
SELECT u.name, p.title, c.text
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE u.created_at > NOW() - INTERVAL 30 DAY;

2. GROUP BY и агрегирующие функции:

-- Подсчёт по группам
SELECT 
    u.id,
    u.name,
    COUNT(p.id) as post_count,
    AVG(p.views) as avg_views,
    MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 0  -- Фильтр ПОСЛЕ GROUP BY
ORDER BY post_count DESC;

3. Подзапросы (subqueries):

-- Найти пользователей, чьи посты получили больше лайков чем средний
SELECT u.*, p.*
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.likes > (
    SELECT AVG(likes) FROM posts
);

-- Подзапрос в FROM (derived table)
SELECT *
FROM (
    SELECT u.id, u.name, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id
) as user_stats
WHERE post_count > 5;

4. Window Functions (продвинуто):

-- Нумерация рядов
SELECT 
    p.title,
    p.likes,
    ROW_NUMBER() OVER (ORDER BY p.likes DESC) as rank
FROM posts p;

-- Рунинг тотал
SELECT 
    DATE(created_at) as date,
    likes,
    SUM(likes) OVER (ORDER BY created_at) as cumulative_likes
FROM posts
ORDER BY created_at;

-- Partition by группы
SELECT 
    u.name,
    p.title,
    p.likes,
    AVG(p.likes) OVER (PARTITION BY u.id) as user_avg_likes
FROM users u
JOIN posts p ON u.id = p.user_id;

Практический пример на Node.js

// Без ORM — используем драйвер БД напрямую
const { Client } = require('pg');
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
});

// Сложный запрос: найти активных пользователей с их постами
async function getActiveUsersWithStats() {
  const query = `
    SELECT 
        u.id,
        u.name,
        u.email,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count,
        MAX(p.created_at) as last_post_date,
        AVG(CAST(p.likes as NUMERIC)) as avg_post_likes
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id AND p.deleted_at IS NULL
    LEFT JOIN comments c ON u.id = c.user_id AND c.deleted_at IS NULL
    WHERE u.deleted_at IS NULL
      AND u.verified = true
      AND (p.created_at > NOW() - INTERVAL '30 days' OR c.created_at > NOW() - INTERVAL '30 days')
    GROUP BY u.id, u.name, u.email
    HAVING COUNT(p.id) > 0 OR COUNT(c.id) > 0
    ORDER BY post_count DESC, comment_count DESC
    LIMIT 100;
  `;
  
  const result = await client.query(query);
  return result.rows;
}

// Параметризованный запрос (для защиты от SQL injection)
async function getUserPostsFilter(userId, minLikes) {
  const query = `
    SELECT p.*, u.name as author_name
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.user_id = $1
      AND p.likes >= $2
      AND p.deleted_at IS NULL
    ORDER BY p.created_at DESC;
  `;
  
  // $1 и $2 подставляются безопасно
  const result = await client.query(query, [userId, minLikes]);
  return result.rows;
}

// Транзакция для сложных операций
async function transferPostsToUser(fromUserId, toUserId) {
  await client.query('BEGIN');
  
  try {
    // Обновляем посты
    await client.query(
      'UPDATE posts SET user_id = $1 WHERE user_id = $2',
      [toUserId, fromUserId]
    );
    
    // Удаляем аккаунт
    await client.query(
      'DELETE FROM users WHERE id = $1',
      [fromUserId]
    );
    
    await client.query('COMMIT');
    console.log('Успешно перенесены посты');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  }
}

На фронтенде: правильные API запросы

// Фронтенд НЕ строит SQL запросы
// Вместо этого отправляет параметры бэкенду

const API_BASE = 'https://api.example.com/api/v1';

// Простой запрос
const users = await fetch(`${API_BASE}/users?page=1&limit=20`)
  .then(r => r.json());

// С фильтрацией
const posts = await fetch(
  `${API_BASE}/posts?userId=123&minLikes=10&sortBy=date`
)
  .then(r => r.json());

// POST с телом
const result = await fetch(`${API_BASE}/reports/generate`, {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    filters: {
      dateFrom: '2024-01-01',
      dateTo: '2024-12-31',
      categories: ['tech', 'business'],
    },
    groupBy: 'category',
    sortBy: 'views desc',
  }),
})
  .then(r => r.json());

Оптимизация сложных запросов

1. Индексы:

-- Быстрый поиск по user_id
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Для JOIN с фильтром
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

-- Для сложных условий
CREATE INDEX idx_users_verified_created ON users(verified, created_at) WHERE deleted_at IS NULL;

2. EXPLAIN для анализа:

EXPLAIN ANALYZE
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.verified = true
GROUP BY u.id;

-- Показывает: время выполнения, используемые индексы, количество строк

3. Материализованные представления:

-- Кеш сложного запроса
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    u.id,
    u.name,
    COUNT(p.id) as post_count,
    AVG(p.likes) as avg_likes
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Обновляем периодически
REFRESH MATERIALIZED VIEW user_stats;

Резюме

Без ORM строятся сложные SQL запросы напрямую с использованием JOIN, GROUP BY, window functions и подзапросов. На фронтенде это не применяется напрямую, но разработчик должен понимать, как бэкенд обрабатывает данные, чтобы правильно отправлять параметры фильтрации. Основные принципы: параметризованные запросы (защита от injection), индексы для производительности, транзакции для целостности данных.