Как в Django через ORM сделать SQL-запрос с INNER JOIN?

Question

[Сергей Еремин]

Вот как такой простой, казалось бы запрос, сделать через ORM?

SELECT  TAB2.option, Count(TAB1.id) AS CountObj
FROM TAB1 INNER JOIN TAB2 ON TAB1.Link = TAB2.id
GROUP BY TAB2.option
ORDER BY Count(TAB1.id) DESC;

Я еще не настоящий сварщик, и пока все что получается тормозит. Проверил -- ORM строит кучу запросов вместо одного GROUP BY. Понять как заставить строить правильно не могу. Какая модель, можно догадаться из запроса... Впрочем, это не суть как важно.

Answer 1

[Anton Kuzmichev]

Вам поможет annotate

Подробнее вот тут: https://docs.djangoproject.com/en/dev/topics/db/ag...
Вот вроде неплохой пример с annotate и сортировкой по count(): stackoverflow.com/questions/1396264/how-to-sort-by...

Comments

[Сергей Еремин]

Изучил, спасибо, все сделал как советуют. Обескуражен:

1. Если делаю все ручками, т.е. выгружаю данные в словарь и там перебором поддсчитываю элементы время выполнения -- 48 секунд:
2. Когда делаю выгрузку TAB2 и по циклу спрашиваю через SQL сколько записей в TAB1 удовлетворяющих условию TAB1.Link = TAB2.id -- 751 секекунд.
3. Когда делаю через агрегацию:

TAB2.objects.annotate(num_in_TAB2=Count('TAB2')).order_by('-num_in_TAB2')

время выполнения -- 827 секунд.

СУБД -- SQLite3
в TAB1 чуть менее 120 тыс. записей
а ТАB2 чуть менее 4800 записей...
Что я делаю не так?

[Anton Kuzmichev]

Сергей Еремин: может попробуете не SQLite, а, например, Postgre?

[Сергей Еремин]

Anton Kuzmichev: :( Я надеялся перейти на серьезную базу поближе к продакшену. Не ожидал что такой несерьезный запрос для SQLite3 окажется тяжелым. Попробую написать raw-запрос в обход ORM: djbook.ru/rel1.4/topics/db/sql.html

И зачем я с этим ORM связался? :(

[Anton Kuzmichev]

Сергей Еремин: да вы знаете, мы делаем 2 больших проекта на Django и пока по пальцам одной руки можно перечислить те места, где используем raw sql, так что "с ORM все норм :)". И группировка у нас есть тоже. У вас же не суперэкзотика какая-то. Если вы все равно планируете перейти на серьёзную СУБД, то установите её сейчас на девелоперскую машину и попробуйте этот же запрос через ORM в ней.

[Anton Kuzmichev]

Сергей Еремин: я вчера перед тем, как вам ответить, сам попробовал составить в консоли джанго запрос по двум связанным моделям одного из проектов. Все прошло успешно и быстро

[Anton Kuzmichev]

Сергей Еремин: .. и смотрел созданный через ORM запрос - вполне себе обычный запрос с одним JOIN'ом, GROUP BY и ORDER BY. Если у вас получится написать на чистом sql для sqlite быстрый запрос - дайте знать, интересно

[Сергей Еремин]

Т.к. я не настоящий сварщик, то для меня SQLite3 хороша тем, что очень просто сделать backup перед любым экспериментом. Чтобы данные не потерять (уж больно долго их парсил с сайта одного министерства).

Я не могу понять почему у меня все так медленно. В прошлой жизни программиста (лет 15 назад, на тех еще машинах) такие данные летали! При том, что SQL-запросы шли в Access через ODBC... Access тоже все данные (и таблицы, и вьюшки, и кеш-запросы, и параметры репликаций) хранит в одном фале... т.е. по сути не должен SQLite3 от него существенно отличаться по производительности.

Мне кажется, что я что-то делаю не так. Но не могу понять, что... :(

[Anton Kuzmichev]

Сергей Еремин: ну вы спарсили данные, у вас есть они в каком-то исходном виде. Залейте их в MySQL/PostgreSQL, также как залили в SQLite, снимите дамп и вот. Теперь если что - накатывайте дамп ваших министерских данных после неудачных экспериментов.

[Сергей Еремин]

Anton Kuzmichev:
Поставил консольное приложение SQLite3. Написал в нем запрос с GROUP BY (на самом деле у меня несколько сложнее база чем в примере выше). Запрос выполняется "мухой"! Так что, получается, не в SQLite3 проблема, а в ORM. Теперь разобраться как этот запрос из Django выполнить. Почему-то через raw не получается.

p = TAB2.objects.raw(
    '''SELECT  TAB2.option, Count(TAB1.id) AS CountObj
       FROM TAB1 INNER JOIN TAB2 ON TAB1.Link = TAB2.id
       GROUP BY TAB2.option
       ORDER BY Count(TAB1.id) DESC;
    ''')

Пишет: Raw query must include the primary key
Похоже это из-за того что обращаюсь: TAB2.objects.raw()
К сожалению в мануалах нет примеров как через модель писать raw-запрос к нескольким таблицам (djbook.ru/rel1.4/topics/db/sql.html). А писать в обход моделей как-то не очень. В мануале рекомендуют так делать только если надо получать доступ к несколькими СУБД одновременно.

Так что остается или разобраться как писать правильный GROUP BY через ORM (в интернетах за это неумение ругают ORM и говорят что он хорошо этого делать не умеет), или разобраться как raw-запрос с GROUP BY вызвать. :(

[Anton Kuzmichev]

Сергей Еремин: ну он просто просит тчобы запрос возвращал первичный ключ, вот так получится?
SELECT TAB2.id, TAB2.option, Count(TAB1.id) AS CountObj ...

[Сергей Еремин]

Anton Kuzmichev: Спасибо! Все получилось! Время выполнения 0.87...1.28 секунды!

Осталось, все таки разобраться как заставить ORM делать такое без raw-запросов.

[Anton Kuzmichev]

Сергей Еремин: посмотрите генерируемый sql для вашей ORM-конструкции и подумайте, что там не так, из-за чего ормовский запрос выполняется так долго, примерно так делается:
TAB2.objects.annotate(num_in_TAB2=Count('TAB2')).order_by('-num_in_TAB2').query.__str__()

[Сергей Еремин]

Anton Kuzmichev:
Что выяснилось. Вместо того, чтобы сформировать запрос который кажется простым и очевидным:

p = TAB2.objects.raw(
    '''SELECT  TAB2.option, Count(TAB1.id) AS num1
       FROM TAB1 INNER JOIN TAB2 ON TAB1.Link = TAB2.id
       GROUP BY TAB2.option
       ORDER BY Count(TAB1.id) DESC;
    ''')

ORM превращает выражение:

TAB2.objects.annotate(num1=Count('TAB2')).order_by('-num1')

с запрос:

p = TAB2.objects.raw(
    '''SELECT TAB2.id, TAB2.option, COUNT(TAB1.id) AS num1
       FROM   TAB2
          LEFT OUTER JOIN TAB1
          ON ( TAB2.id = TAB1.Link )
       GROUP BY TAB2.id, TAB2.option
       ORDER BY num1 DESC;
    ''')

И этот запрос медленнее в 500 раз. Я, конечно, не настоящий сварщик, но из прошлой жизни программирования мне кажется что это из-за реализации OUTER JOIN в SQLite3... похоже он "перемножает" таблицы, а суммарную таблицу 100000x5000 записей обрабатывать реально дольше чем просто 100000 записей получаемый при INNER JOIN. Не понятно почему ORM пишет OUTER JOIN :(

[Сергей Еремин]

Anton Kuzmichev:
Гипотеза не подтвердилась. INNER JOIN и OUTER JOIN выполняются с одинаковой скоростью. Очевидно, проблема в чем-то другом. В реале у меня в TAB2 одна ссылка на саму себя (отношение записей родитель-потомок внутри одной таблице). Т.к. ORM строит SELECT по всем полям, и после еще по этим полям нужно GROUP BY то наверное вот эта ссылка саму-на себя и тормозит (хотя, вообще-то кажется это бред). В общем, оставлю raw запрос.

[Андрей]

Сергей Еремин: поздновато, но ладно. Выбираемые поля можно ограничить, и тогда будет счастье. правда, вернется вам не полноценный список объектов, а список из dict, с нужными полями

[Андрей]

Через values или values_list в самом конце