Аннотирование набора запросов Django с левым внешним соединением?

Скажем, у меня есть модель:

class Foo(models.Model):
    ...

и другая модель, которая в основном дает информацию для каждого пользователя о Foo:

class UserFoo(models.Model):
    user = models.ForeignKey(User)
    foo = models.ForeignKey(Foo)
    ...

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

Я хотел бы сгенерировать набор запросов Foo, но аннотированный с помощью (необязательного) связанного UserFoo на основе user=request.user.

Таким образом, эффективно LEFT OUTER JOIN on (foo.id = userfoo.foo_id AND userfoo.user_id = ...)

Ответ 1

Решение с raw может выглядеть как

foos = Foo.objects.raw("SELECT foo.* FROM foo LEFT OUTER JOIN userfoo ON (foo.id = userfoo.foo_id AND foo.user_id = %s)", [request.user.id])

Вам нужно будет изменить SELECT, чтобы включить дополнительные поля из userfoo, которые будут аннотированы к результирующим экземплярам Foo в наборе запросов.

Ответ 2

Примечание: Этот метод не работает в Django 1.6+. Как пояснялось в комментарии tcarobruce ниже, аргумент promote был удален как часть ticket # 19849: Очистка ORM.

Django не предоставляет полностью встроенный способ сделать это, но не обязательно создавать полностью необработанный запрос. (Этот метод не работает для выбора * из UserFoo, поэтому я использую .comment как примерное поле для включения из UserFoo.)

QuerySet.extra() method позволяет добавлять термины к предложениям SELECT и WHERE нашего запроса. Мы используем это для включения полей из таблицы UserFoo в наши результаты и ограничиваем соответствие UserFoo текущему пользователю.

results = Foo.objects.extra(
    select={"user_comment": "UserFoo.comment"},
    where=["(UserFoo.user_id IS NULL OR UserFoo.user_id = %s)"],
    params=[request.user.id]
)

Этот запрос по-прежнему нуждается в таблице UserFoo. Можно было бы использовать .extras(tables=...) для получения неявного INNER JOIN, но для OUTER JOIN нам нужно изменить внутренний объект запроса.

connection = (
    UserFoo._meta.db_table, User._meta.db_table,  # JOIN these tables
    "user_id",              "id",                 # on these fields
)

results.query.join(  # modify the query
    connection,      # with this table connection
    promote=True,    # as LEFT OUTER JOIN
)

Теперь мы можем оценить результаты. Каждый экземпляр будет иметь свойство .user_comment, содержащее значение из UserFoo или None, если оно не существует.

print results[0].user_comment

(Подпишитесь на это сообщение в блоге от Колина Коупленда за то, что он показал мне, как делать ВНЕШНИЕ СОБЫТИЯ.)

Ответ 3

Этот ответ может быть не совсем то, что вы ищете, но с момента его первого результата в google при поиске "django annotate external join", поэтому я отправлю его здесь.

Примечание: проверено на Djang 1.7

Предположим, что у вас есть следующие модели

class User(models.Model):
    name = models.CharField()

class EarnedPoints(models.Model):
    points = models.PositiveIntegerField()
    user = models.ForgeinKey(User)

Чтобы получить общее количество очков пользователя, вы можете сделать что-то вроде этого

 User.objects.annotate(points=Sum("earned_points__points"))

это будет работать, но он будет не возвращать пользователей, у которых нет точек, здесь нам нужно внешнее соединение без каких-либо прямых хаков или raw sql

Вы можете добиться этого, выполнив это

 users_with_points = User.objects.annotate(points=Sum("earned_points__points"))
 result = users_with_points | User.objects.exclude(pk__in=users_with_points)

Это будет переведено в ВЗАИМОДЕЙНУЮ ВСПОМОГАТЕЛЬНУЮ, и все пользователи будут возвращены. пользователи, у которых нет точек, будут иметь значение None в своем атрибуте точек.

Надеюсь, что поможет

Ответ 4

Два запроса, которые вы предлагаете, так же хороши, как и вы (без использования raw()), этот тип запроса в ORM не представляется в настоящее время.

Ответ 5

Я наткнулся на эту проблему, которую не смог решить, не прибегая к необработанному SQL, но я не хотел переписывать весь запрос.

Ниже приведено описание того, как вы можете увеличить набор запросов с помощью внешнего raw sql, не заботясь о фактическом запросе, который генерирует запрос.

Вот типичный сценарий: у вас есть подобный reddit сайт с моделью LinkPost и режимом UserPostVote, например:

class LinkPost(models.Model):
some fields....

class UserPostVote(models.Model):
    user = models.ForeignKey(User,related_name="post_votes")
    post = models.ForeignKey(LinkPost,related_name="user_votes")
    value = models.IntegerField(null=False, default=0)

где таблица userpostvote собирает голоса пользователей на сообщениях. Теперь вы пытаетесь отобразить первую страницу для пользователя с помощью приложения для разбивки на страницы, но вы хотите, чтобы стрелки были красными для сообщений, на которые голосовал пользователь.

Сначала вы получаете сообщения для страницы:

post_list = LinkPost.objects.all()
paginator = Paginator(post_list,25)
posts_page = paginator.page(request.GET.get('page'))

так что теперь у вас есть страница сообщений QuerySet, созданная с помощью dagango paginator, которая выбирает сообщения для отображения. Как мы теперь добавляем аннотацию голосования пользователя на каждое сообщение перед его отображением в шаблоне?

Здесь, где это сложно, и я не смог найти чистое решение ORM. select_related не позволит вам получать только голоса, соответствующие зарегистрированному пользователю, и зацикливание над сообщениями будет выполнять запросы на связку, а не одно, и делать все это необработанным, мы не сможем использовать запрос из приложения для разбивки на страницы.

Итак, вот как я это делаю:

q1 = posts_page.object_list.query  # The query object of the queryset
q1_alias = q1.get_initial_alias()  # This forces the query object to generate it sql
(q1str, q1param) = q1.sql_with_params() #This gets the sql for the query along with 
                                        #parameters, which are none in this example

Теперь у нас есть запрос для набора запросов и просто оберните его, псевдоним и оставшееся внешнее соединение:

q2_augment = "SELECT B.value as uservote, A.* 
from ("+q1str+") A LEFT OUTER JOIN reddit_userpostvote B 
ON A.id = B.post_id AND B.user_id = %s"
q2param = (request.user.id,)
posts_augmented = LinkPost.objects.raw(q2_augment,q1param+q2param)

вуаля! Теперь мы можем получить доступ к post.uservote для сообщения в расширенном наборе запросов. И мы просто попадаем в базу данных с одним запросом.

Ответ 6

Вы можете сделать это, используя simonw django-queryset-transform, чтобы избежать жесткого кодирования необработанного SQL-запроса - код выглядел бы примерно так:

def userfoo_retriever(qs):
    userfoos = dict((i.pk, i) for i in UserFoo.objects.filter(foo__in=qs))
    for i in qs:
        i.userfoo = userfoos.get(i.pk, None)

for foo in Foo.objects.filter(…).tranform(userfoo_retriever):
    print foo.userfoo

Этот подход был довольно успешным для этой потребности и для эффективного извлечения значений M2M; ваш счетчик запросов будет не таким низким, но в некоторых базах данных (кашель MySQL кашля) выполнение двух более простых запросов часто может быть быстрее, чем один со сложными JOIN, и многие из случаев, когда я больше всего нуждался в этом, имел дополнительную сложность, которая имела бы было еще труднее взломать выражение ORM.

Ответ 7

Что касается внешних подключений: Когда у вас есть запрос qs из foo, который включает ссылку на столбцы из userfoo, вы можете продвигать внутреннее соединение к внешнему соединению с помощью qs.query.promote_joins(["userfoo"])

Ответ 8

Вы не должны прибегать к extra или raw для этого.

Следующее должно работать.

Foo.objects.filter(
    Q(userfoo_set__user=request.user) |
    Q(userfoo_set=None)  # This forces the use of LOUTER JOIN.
).annotate(
    comment=F('userfoo_set__comment'),
    # ... annotate all the fields you'd like to see added here.
)

Ответ 9

Комментарий maparent поставил меня на правильный путь:

from django.db.models.sql.datastructures import Join

for alias in qs.query.alias_map.values():
  if isinstance(alias, Join):
    alias.nullable = True

qs.query.promote_joins(qs.query.tables)