Django Left Outer Join

У меня есть сайт, на котором пользователи могут видеть список фильмов и создавать отзывы для них.

Пользователь должен иметь возможность видеть список всех фильмов. Кроме того, ЕСЛИ они просмотрели фильм, они должны иметь возможность увидеть оценку, которую они дали. Если нет, фильм просто отображается без оценки.

Они не заботятся о оценках, предоставляемых другими пользователями.

Рассмотрим следующее models.py

from django.contrib.auth.models import User
from django.db import models


class Topic(models.Model):
    name = models.TextField()

    def __str__(self):
        return self.name


class Record(models.Model):
    user = models.ForeignKey(User)
    topic = models.ForeignKey(Topic)
    value = models.TextField()

    class Meta:
        unique_together = ("user", "topic")

То, что я действительно хочу, это

select * from bar_topic
left join (select topic_id as tid, value from bar_record where user_id = 1)
on tid = bar_topic.id

Рассмотрим следующий test.py для контекста:

from django.test import TestCase

from bar.models import *


from django.db.models import Q

class TestSuite(TestCase):

    def setUp(self):
        t1 = Topic.objects.create(name="A")
        t2 = Topic.objects.create(name="B")
        t3 = Topic.objects.create(name="C")
        # 2 for Johnny
        johnny = User.objects.create(username="Johnny")
        johnny.record_set.create(topic=t1, value=1)
        johnny.record_set.create(topic=t3, value=3)
        # 3 for Mary
        mary = User.objects.create(username="Mary")
        mary.record_set.create(topic=t1, value=4)
        mary.record_set.create(topic=t2, value=5)
        mary.record_set.create(topic=t3, value=6)

    def test_raw(self):
        print('\nraw\n---')
        with self.assertNumQueries(1):
            topics = Topic.objects.raw('''
                select * from bar_topic
                left join (select topic_id as tid, value from bar_record where user_id = 1)
                on tid = bar_topic.id
                ''')
            for topic in topics:
                print(topic, topic.value)

    def test_orm(self):
        print('\norm\n---')
        with self.assertNumQueries(1):
            topics = Topic.objects.filter(Q(record__user_id=1)).values_list('name', 'record__value')
            for topic in topics:
                print(*topic)

BOTH тесты должны печатать тот же самый результат, однако только исходная версия выводит правильную таблицу результатов:

raw
---
A 1
B None
C 3

вместо этого орма возвращает этот

orm
---
A 1
C 3

Любая попытка объединить остальные темы, те, которые не имеют отзывов от пользователя "johnny", приводят к следующему:

orm
---
A 1
A 4
B 5
C 3
C 6

Как я могу выполнить простое поведение необработанного запроса с помощью ORM Django?

edit: Этот вид работ, но кажется очень плохим:

topics = Topic.objects.filter(record__user_id=1).values_list('name', 'record__value')
noned = Topic.objects.exclude(record__user_id=1).values_list('name')
for topic in chain(topics, noned):
    ...

edit: Это работает немного лучше, но все равно плохо:

    topics = Topic.objects.filter(record__user_id=1).annotate(value=F('record__value'))
    topics |= Topic.objects.exclude(pk__in=topics)
orm
---
A 1
B 5
C 3

Ответы

Ответ 1

Прежде всего, нет способа (атм Django 1.9.7) иметь представление с Django ORM необработанного запроса, который вы разместили, именно так, как вы хотите; хотя, вы можете получить тот же желаемый результат с чем-то вроде:

>>> Topic.objects.annotate(
        f=Case(
            When(
                record__user=johnny, 
                then=F('record__value')
            ), 
            output_field=IntegerField()
        )
    ).order_by(
        'id', 'name', 'f'
    ).distinct(
        'id', 'name'
    ).values_list(
        'name', 'f'
    )
>>> [(u'A', 1), (u'B', None), (u'C', 3)]

>>> Topic.objects.annotate(f=Case(When(record__user=may, then=F('record__value')), output_field=IntegerField())).order_by('id', 'name', 'f').distinct('id', 'name').values_list('name', 'f')
>>> [(u'A', 4), (u'B', 5), (u'C', 6)]

Вот SQL, сгенерированный для первого запроса:

>>> print Topic.objects.annotate(f=Case(When(record__user=johnny, then=F('record__value')), output_field=IntegerField())).order_by('id', 'name', 'f').distinct('id', 'name').values_list('name', 'f').query

>>> SELECT DISTINCT ON ("payments_topic"."id", "payments_topic"."name") "payments_topic"."name", CASE WHEN "payments_record"."user_id" = 1 THEN "payments_record"."value" ELSE NULL END AS "f" FROM "payments_topic" LEFT OUTER JOIN "payments_record" ON ("payments_topic"."id" = "payments_record"."topic_id") ORDER BY "payments_topic"."id" ASC, "payments_topic"."name" ASC, "f" ASC

Некоторые заметки

  • Не стесняйтесь использовать необработанные запросы, особенно когда производительность важнее всего. Более того, иногда это просто необходимо, поскольку вы не можете получить тот же результат, используя Django ORM; в других случаях вы можете, но время от времени наличие чистого и понятного кода важнее, чем производительность этого фрагмента кода.
  • distinct этом ответе используется distinct от позиционных аргументов, который доступен только для PostgreSQL, atm. В документации вы можете увидеть больше об условных выражениях.

Ответ 2

Вот как я это сделаю. Два запроса, а не один:

class Topic(models.Model):
    #...

    @property
    def user_value(self):
        try:
            return self.user_records[0].value
        except IndexError:
            #This topic does not have 
            #a review by the request.user
            return None
        except AttributeError:
            raise AttributeError('You forgot to prefetch the user_records')
            #or you can just
            return None

#usage
topics = Topic.objects.all().prefetch_related(
    models.Prefetch('record_set',
        queryset=Record.objects.filter(user=request.user),
        to_attr='user_records'
    )
)

for topic in topics:
    print topic.user_value

Преимущество состоит в том, что вы получаете весь объект Record. Поэтому рассмотрите ситуацию, когда вы хотите не только показать value, но и time-stamp.

Только для записи я хочу показать еще одно решение, используя .extra. Я впечатлен тем, что никто не упоминает об этом, потому что он должен обеспечить наилучшую производительность.

topics = Topic.objects.all().extra(
    select={
        'user_value': """SELECT value FROM myapp_record 
            WHERE myapp_record.user_id = %s
            AND myapp_record.topic_id = myapp_topic.id 
        """
    },
    select_params=(request.user.id,)
)

for topic in topics
    print topic.user_value

Оба решения могут быть абстрагированы в пользовательский класс TopicQuerySet для повторного использования.

class TopicQuerySet(models.QuerySet):

    def prefetch_user_records(self, user):
        return self.prefetch_related(
            models.Prefetch('record_set',
                queryset=Record.objects.filter(user=request.user),
                to_attr='user_records'
            )
        )

    def annotate_user_value(self, user):
        return self.extra(
            select={
                'user_value': """SELECT value FROM myapp_record 
                    WHERE myapp_record.user_id = %s
                    AND myapp_record.topic_id = myapp_topic.id 
                """
            },
            select_params=(user.id,)
        )

class Topic(models.Model):
    #...

    objects = TopicQuerySet.as_manager()


#usage
topics = Topic.objects.all().annotate_user_value(request.user)
#or
topics = Topic.objects.all().prefetch_user_records(request.user)

for topic in topics:
    print topic.user_value

Ответ 3

Решение вдохновлено trinchet answer

>>> qs = Topic.objects.annotate(
...         f=Max(Case(When(record__user=johnny, then=F('record__value'))))
... )
>>> print(qs.values_list('name', 'f'))
[(u'A', 1), (u'B', None), (u'C', 3)]
>>> print(qs.query)  # formated and removed excessive double quotes
SELECT bar_topic.id, bar_topic.name,
       MAX(CASE WHEN bar_record.user_id = 1 THEN bar_record.value ELSE NULL END) AS f
FROM bar_topic LEFT OUTER JOIN bar_record ON (bar_topic.id = bar_record.topic_id)
GROUP BY bar_topic.id, bar_topic.name

Преимущество

  • Он также работает с SQLite.
  • Набор запросов можно легко фильтровать или сортировать, независимо от того, как.
  • Не требуется тип output_field.
  • Методы values или values_list(*field_names) полезны для более простого GROUP BY, но они не нужны.

Левое соединение можно сделать более читаемым, написав функцию:

from django.db.models import Max, Case, When, F

def left_join(result_field, **lookups):
    return Max(Case(When(then=F(result_field), **lookups)))

>>> Topic.objects.annotate(
...         record_value=left_join('record__value', record__user=johnny),
... ).values_list('name', 'record_value')

Дополнительные поля из записи могут быть добавлены методом anotate для получения результатов с помощью хороших мнемонических имен.

Я согласен с другими авторами в том, что его можно оптимизировать, но показатель удобочитаемости.

EDIT: тот же результат появляется, если функция агрегации Max заменяется на Min. И Min, и Max игнорируют значения NULL и могут использоваться для любого типа, например. для строк. Агрегация полезна, если левое соединение не гарантировано уникально. Если поле числовое, то может быть полезно использовать среднее значение Avg в левом объединении.

Ответ 4

То, что я действительно хочу, это

select * from bar_topic
left join (select topic_id as tid, value from bar_record where user_id = 1)
on tid = bar_topic.id

... или, возможно, этот эквивалент, который позволяет избежать подзапроса...

select * from bar_topic
left join bar_record
on bar_record.topic_id = bar_topic.id and bar_record.user_id = 1

Я хочу знать, как это сделать эффективно, или, если это невозможно, объяснение, почему это невозможно...

Если вы не используете необработанные запросы, это невозможно с помощью Django ORM, и вот почему.

QuerySet objects (django.db.models.query.QuerySet) имеют атрибут query (django.db.models.sql.query.Query), который представляет собой фактический запрос, который будет выполнен. Эти query объекты имеют метод __str__, поэтому вы можете распечатать его, чтобы узнать, что это такое.

Начнем с простого QuerySet...

>>> from bar.models import *
>>> qs = Topic.objects.filter(record__user_id=1)
>>> print qs.query
SELECT "bar_topic"."id", "bar_topic"."name" FROM "bar_topic" INNER JOIN "bar_record" ON ("bar_topic"."id" = "bar_record"."topic_id") WHERE "bar_record"."user_id" = 1

... который, очевидно, не будет работать, из-за INNER JOIN.

Взяв более глубокий взгляд внутри объекта query, существует атрибут alias_map, который определяет, какие таблицы будут выполняться...

>>> from pprint import pprint
>>> pprint(qs.query.alias_map)
{u'bar_record': JoinInfo(table_name=u'bar_record', rhs_alias=u'bar_record', join_type='INNER JOIN', lhs_alias=u'bar_topic', lhs_join_col=u'id', rhs_join_col='topic_id', nullable=True),
 u'bar_topic': JoinInfo(table_name=u'bar_topic', rhs_alias=u'bar_topic', join_type=None, lhs_alias=None, lhs_join_col=None, rhs_join_col=None, nullable=False),
 u'auth_user': JoinInfo(table_name=u'auth_user', rhs_alias=u'auth_user', join_type='INNER JOIN', lhs_alias=u'bar_record', lhs_join_col='user_id', rhs_join_col=u'id', nullable=False)}

Обратите внимание, что Django поддерживает только два возможных join_type s, INNER JOIN и LEFT OUTER JOIN.

Теперь мы можем использовать методы query object promote_joins для использования LEFT OUTER JOIN в таблице bar_record...

>>> qs.query.promote_joins(['bar_record'])
>>> pprint(qs.query.alias_map)
{u'bar_record': JoinInfo(table_name=u'bar_record', rhs_alias=u'bar_record', join_type='LEFT OUTER JOIN', lhs_alias=u'bar_topic', lhs_join_col=u'id', rhs_join_col='topic_id', nullable=True),
 u'bar_topic': JoinInfo(table_name=u'bar_topic', rhs_alias=u'bar_topic', join_type=None, lhs_alias=None, lhs_join_col=None, rhs_join_col=None, nullable=False),
 u'auth_user': JoinInfo(table_name=u'auth_user', rhs_alias=u'auth_user', join_type='LEFT OUTER JOIN', lhs_alias=u'bar_record', lhs_join_col='user_id', rhs_join_col=u'id', nullable=False)}

... который изменит запрос на...

>>> print qs.query
SELECT "bar_topic"."id", "bar_topic"."name" FROM "bar_topic" LEFT OUTER JOIN "bar_record" ON ("bar_topic"."id" = "bar_record"."topic_id") WHERE "bar_record"."user_id" = 1

... однако это все равно бесполезно, так как соединение всегда будет соответствовать строке, даже если оно не принадлежит правильному пользователю, а предложение WHERE отфильтровывает его.

Использование values_list() автоматически влияет на join_type...

>>> qs = Topic.objects.filter(record__user_id=1).values_list('name', 'record__value')
>>> print qs.query
SELECT "bar_topic"."name", "bar_record"."value" FROM "bar_topic" LEFT OUTER JOIN "bar_record" ON ("bar_topic"."id" = "bar_record"."topic_id") WHERE "bar_record"."user_id" = 1

... но в конечном итоге страдает от одной и той же проблемы.

К сожалению, существует фундаментальное ограничение в объединениях, генерируемых ORM, в том, что они могут быть только формы...

(LEFT OUTER|INNER) JOIN <lhs_alias> ON (<lhs_alias>.<lhs_join_col> = <rhs_alias>.<rhs_join_col>)

... так что на самом деле нет способа добиться желаемого SQL, кроме использования необработанного запроса.

Конечно, вы можете взломать вещи типа annotate() и extra(), но они, скорее всего, будут генерировать запросы, которые намного менее эффективны и, возможно, более читабельны, чем исходный SQL.

... и предлагаемая альтернатива.

Лично я просто использую необработанный запрос...

select * from bar_topic
left join bar_record
on bar_record.topic_id = bar_topic.id and bar_record.user_id = 1

..., который достаточно прост, чтобы быть совместимым со всеми поддерживаемыми Django бэкэндами.

Ответ 5

Необработанные запросы.

topics = Topic.objects.raw('''
            select * from bar_topic
            left join (select topic_id as tid, value from bar_record where user_id = 1) AS subq
            on tid = bar_topic.id
            ''')

Кажется, вы сами знаете ответ. Нет ничего плохого в использовании необработанного запроса, если вы не можете заставить запрос ORM вести себя так, как вы хотите.

Один главный недостаток необработанных запросов заключается в том, что они не кэшируются, как запросы ORM. Это означает, что если вы повторите попытку с помощью запроса rawset дважды, запрос будет повторен. Другим является то, что вы не можете вызвать .count() на нем.

Нулевые внешние ключи

Вы можете заставить ORM использовать LEFT OUTER JOIN BY, установив null=True в внешние ключи. Сделайте это с помощью таблиц, как они есть.

print Record.objects.filter(user_id=8).select_related('topic').query

Результат

SELECT "bar_record"."id", "bar_record"."user_id", "bar_record"."topic_id", "bar_record"."value", "bar_topic"."id", "bar_topic"."name" FROM "bar_record"
INNER JOIN "bar_topic" ON ( "bar_record"."topic_id" = "bar_topic"."id" ) WHERE "bar_record"."user_id" = 8

Теперь установите значение null = True и выполните тот же запрос ORM, что и выше. Результатом является

SELECT "bar_record"."id", "bar_record"."user_id", "bar_record"."topic_id", "bar_record"."value", "bar_topic"."id", "bar_topic"."name" FROM "bar_record" 
LEFT OUTER JOIN "bar_topic" ON ( "bar_record"."topic_id" = "bar_topic"."id" ) WHERE "bar_record"."user_id" = 8

Обратите внимание, что запрос неожиданно изменился на LEFT OUTER JOIN. Но мы еще не из леса, потому что порядок столов должен быть отменен! Таким образом, если вы не можете реструктурировать свои модели, ORM LEFT OUTER JOIN может оказаться невозможным без цепочки или UNION, оба из которых вы уже пробовали.