Маниакальный веблог » Последовательные join'ы

Сегодня в Django'вском списке рассылки вычитал об одной штуке, которая неочевидна из документации, но тем не менее дико крута. Нельзя такое прятать!

У вас в базе есть 3 таблички: Компания, Отдел и Сотрудник, которые последовательно связаны друг с другом отношениями один-ко-многим: совершенно обычная и часто встречающаяся модель. В Django это описывается примерно так:

class Company(meta.Model):
  name=meta.CharField(maxlength=20)

class Department(meta.Model):
  company=meta.ForeignKey(Company)
  name=meta.CharField(maxlength=20)

class Employee(meta.Model):
  department=meta.ForeignKey(Department)
  name=meta.CharField(maxlength=20)

И нам нужны все сотрудники какой-то определенной компании. Поскольку у Сотрудника нет прямой ссылки на Компанию, придется объединить таблички, и достать Компанию через Отдел.

Если делать SQL'ом, то это, очевидно, выглядит как-то так:

SELECT
  employees.id, employees.name
FROM
  employees, departments
WHERE
  department_id=departments.id AND
  company_id=1

Несмотря на то, что все просто, такие ссылки через таблицы добавляют очень много мусора в SQL, и эти запросы потом трудно разбирать: не сразу видно, какие таблички в запросе интересны, а какие просто вспомогательные для ссылок.

Но в Django есть API доступа к БД, который, по идее, должен это упрощать. Из документации прямо следует только то, что можно автоматически получить непосредственно связанные объекты (например company.get_department_list()), а вот через таблицу, вроде бы, нет средств. Но оказывается, можно:

employees.get_list(department__company__pk=1)

И все! Вот это "department__company" автоматически построит нужные join'ы. Очень, на мой взгляд, элегантно и читаемо.

А вот какой реальный SQL строится по этому вызову (я только отформатировал для удобства чтения):

SELECT 
  "test_employees"."id",
  "test_employees"."department_id",
  "test_employees"."name" 
FROM 
  "test_employees",
  "test_departments" "t1" 
WHERE 
  "test_employees"."department_id" = "t1"."id" AND 
  "t1"."company_id" = 1

Комментарии: 4

Дима
Вообще-то я думаю что намного эффективнее будет запрос вида
```
SELECT ...
FROM test_employes e1
INNER JOIN test_departments t1
ON e1.department_id = t1.id AND t1.company_id = 1
```
Последнее условие (вместо AND) впрочем можно запихнуть в условие WHERE, если очень нравится.
Sergey Yanovitsky
Если уж гворить о "мусоре" в базе данных при join-ах, то здесь с точки зрения SQL составитель базы сам себе злобный буратина.
Из документации SQL явно следует (но никто на это пальцем не тыкает) что если есть в таблице два поля с одинаковым именем то конструкция NATURAL JOIN может объединить значения таблиц по этому полю (не забываем что запятая в списке таблиц на самом деле syntactic sugar для INNER JOIN).
Почему этим никто не пользуется?
Очень просто. Многие хорошие разработчики в основном знакомы с базами постольку-поскольку и тяготеют к написанию конструкций типа (пример для postgreSQL)
```
CREATE TABLE company (
    id     serial PRIMARY KEY,
    name    varchar
);

CREATE TABLE department (
    id          serial PRIMARY KEY,
    company_id  int4,
    name        varchar,

    FOREIGN KEY ( company_id )
    REFERENCES company( id )
        ON DELETE CASCADE
        ON UPDATE CASCADE

);
```
Это и приводит к построению запросов через декартово произведение с фильтрацией => муторному SQL.

Хотя если обеспечить уникальность имен основных ключей таблиц (а еще лучше всех полей для избежания ошибок типа ambigous column ) например префиксами то получим следующую картину (пишу для всех таблиц)
```
CREATE TABLE company (
    co_id   serial PRIMARY KEY,
    co_name varchar
);

CREATE TABLE department (
    d_id            serial PRIMARY KEY,
    co_id       int4,
    d_name      varchar,

    FOREIGN KEY ( co_id )
    REFERENCES company( co_id )
        ON DELETE CASCADE
        ON UPDATE CASCADE

);

CREATE TABLE employee (
    e_id            serial PRIMARY KEY,
    d_id            int4,
    e_name      varchar,

    FOREIGN KEY ( d_id )
    REFERENCES department( d_id )
        ON DELETE CASCADE
        ON UPDATE CASCADE

);
```
Результирующий запрос получится в виде
```
SELECT
    e_id,
    d_id,
    e_name
FROM
    company NATURAL JOIN
    department NATURAL JOIN
    employee
WHERE
    co_id = 1;
```
Здесь без писания лишних конструкций оч. хорошо видно что и откуда берется. Для дебуганья базы вручную (скажем надо что-то сделать напрямую через консоль) этот метод тоже черезвычайно хорош - количество ошибок и время составления сложных запросов падает на порядок.

Планировщик-оптимизатор запросов запросы с запятой и запросы с NATURAL JOIN воспринимает одинаково и если у вас порядок в запросе указан не оптимальный - он подгонит его до оптимума. Результат - одинаковый execution plan в обоих случаях.

А теперь о плохом. Если например в active record пользовать такие таблицы то scaffold будет выдавать на страницы несколько не красивый код (у них свое понятие значения префиксов).

С другой стороны если мы пишем EJB то прийдется написать в XML полный маппинг всех полей на переменные классов (правда больше ничего делать не надо). Зато если базой пользуется не только web-приложение а еще и внешние приложения - толк будет.
Sergey Yanovitsky
Поскольку в моем предыдущем примере co_id есть и в компании и в департаменте результирующий запрос можно упростить на одно объединение (если co_id известен заранее):
```
SELECT
    e_id,
    d_id,
    e_name
FROM
    department NATURAL JOIN
    employee
WHERE
    co_id = 1;
```
Получится почти как у Димы но на мой взгляд более удобочитаемо. :)
Давид Мзареулян

Sergey Yanovitsky — венгерская нотация живее всех живых…:)

Последовательные join'ы

Комментарии: 4

Добавить комментарий