python - queryset - filter django

Django Left Outer Join

Question

Tengo un sitio web donde los usuarios pueden ver una lista de películas y crear revisiones para ellos.

El usuario debe poder ver la lista de todas las películas. Además, SI han revisado la película, deberían poder ver la puntuación que le dieron. Si no, la película se muestra sin la puntuación.

No les importa en absoluto las puntuaciones proporcionadas por otros usuarios.

Considera los siguientes 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")

Lo que esencialmente quiero es esto

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

Considere el siguiente test.py para el contexto:

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)

AMBAS pruebas deben imprimir exactamente el mismo resultado; sin embargo, solo la versión en bruto muestra la tabla de resultados correcta:

raw --- A 1 B None C 3

el orm devuelve esto

orm --- A 1 C 3

Cualquier intento de unir el resto de los temas, aquellos que no tienen comentarios del usuario "johnny", resulta en lo siguiente:

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

¿Cómo puedo lograr el comportamiento simple de la consulta sin formato con el ORM de Django?

Edición: Este tipo de obras, pero parece muy pobre:

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): ...

Edición: Esto funciona un poco mejor, pero sigue siendo malo:

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

Answer 1

Lo que esencialmente quiero es esto

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

... o, tal vez este equivalente que evita una subconsulta ...

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

Quiero saber cómo hacer esto de manera efectiva o, si es imposible, una explicación de por qué es imposible ...

A menos que use consultas en bruto, es imposible con el ORM de Django, y he aquí por qué.

QuerySet objetos QuerySet ( django.db.models.query.QuerySet ) tienen un atributo de query ( django.db.models.sql.query.Query ) que es una representación de la consulta real que se realizará. Estos objetos Query tienen un método __str__ , por lo que puede imprimirlo para ver qué es.

Vamos a empezar con un simple 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

... lo que obviamente no va a funcionar, debido a la INNER JOIN .

Al observar más detenidamente el objeto Query , hay un atributo alias_map que determina qué combinaciones de tabla se realizarán ...

>>> 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)}

Tenga en cuenta que Django solo admite dos join_type posibles, INNER JOIN y LEFT OUTER JOIN .

Ahora, podemos usar los métodos de promote_joins del objeto de promote_joins para usar una LEFT OUTER JOIN bar_record LEFT OUTER JOIN en la tabla 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)}

... lo que cambiará la consulta a ...

>>> 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

... sin embargo, esto todavía no sirve, ya que la unión siempre coincidirá con una fila, incluso si no pertenece al usuario correcto, y la cláusula WHERE filtrará.

El uso de values_list() influye automáticamente en el 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

... pero al final sufre el mismo problema.

Desafortunadamente, existe una limitación fundamental en las uniones generadas por el ORM, ya que solo pueden ser de la forma ...

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

... así que realmente no hay manera de lograr el SQL deseado, aparte de usar una consulta en bruto.

Claro, puede piratear con cosas como annotate() y extra() , pero es probable que generen consultas que tienen mucho menos rendimiento y posiblemente no más legibles que el SQL sin formato.

... y una alternativa sugerida.

Personalmente, solo usaría la consulta cruda ...

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

... que es lo suficientemente simple como para ser compatible con todos los backends compatibles de Django.

Answer 2

Consultas en bruto.

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
            '''''')

Parece que sabes la respuesta tú mismo. No hay nada de malo en usar una consulta sin formato cuando no puede lograr que la consulta de ORM se comporte exactamente de la manera que desea.

Un inconveniente principal de las consultas sin formato es que no se almacenan en la memoria caché como las consultas de ORM. Eso significa que si se itera dos veces en el conjunto de consultas sin procesar, la consulta se repetirá. Otra es que no puedes llamar a .count () en él.

Claves foráneas nulas

Puede forzar al ORM a usar la configuración IZQUIERDA EXTERNA IZQUIERDA null=True en las claves externas. Haz esto con las tablas como son.

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

El resultado es

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

Ahora establezca, null = True y realice la misma consulta ORM que arriba. El resultado es

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

Observe cómo la consulta ha cambiado repentinamente a LEFT OUTER JOIN . ¡Pero todavía no estamos fuera de peligro porque el orden de las tablas debería invertirse! Por lo tanto, a menos que pueda reestructurar sus modelos, un ORM IEFT OUTER JOIN puede no ser del todo posible sin el encadenamiento o UNION, que ya ha probado.

Answer 3

Así es como lo haría. Dos consultas, no una:

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

El beneficio es que obtienes el objeto Record completo. Entonces, considere una situación en la que no solo desee mostrar el value , sino también la time-stamp .

Solo para el registro quiero mostrar una solución más usando .extra . Estoy impresionado de que nadie lo haya mencionado, porque debería producir el mejor rendimiento posible.

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

Ambas soluciones pueden TopicQuerySet en una clase personalizada de TopicQuerySet para su reutilización.

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

Answer 4

En primer lugar, no hay una manera (atm Django 1.9.7) de tener una representación con el ORM de Django de la consulta sin formato que ha publicado, exactamente como lo desea ; aunque, puedes obtener el mismo resultado deseado con algo como:

>>> 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)]

Aquí el SQL generado para la primera consulta:

>>> 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

Algunas notas

• No dude en utilizar consultas en bruto, especialmente cuando el rendimiento es lo más importante. Además, a veces es imprescindible ya que no se puede obtener el mismo resultado utilizando el ORM de Django; en otros casos puede hacerlo, pero de vez en cuando tener un código limpio y comprensible es más importante que el rendimiento en esta pieza de código.
• Se usa distinct con argumentos posicionales en esta respuesta, que está disponible solo para PostgreSQL, atm. En los documentos puedes ver más sobre expresiones condicionales .

Answer 5

La solución está inspirada en la respuesta de trinchet.

>>> 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

Ventaja

• Funciona también con SQLite.
• El conjunto de consultas se puede filtrar o ordenar fácilmente, sin importar cómo.
• No es necesario escribir el tipo output_field .
• Los values métodos o values_list(*field_names) values values_list(*field_names) son útiles para un GROUP BY , pero no son necesarios.

La unión izquierda se puede hacer más legible escribiendo una función:

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'')

Se pueden agregar más campos de Registro por el método anotate a los resultados de esta manera con buenos nombres mnemónicos.

Estoy de acuerdo con otros autores en que se puede optimizar, pero la legibilidad cuenta .

EDITAR : El mismo resultado se obtiene si la función de agregación Max se reemplaza por Min . Tanto Min como Max ignoran los valores NULL y pueden usarse en cualquier tipo, por ejemplo, para cadenas. La agregación es útil si no se garantiza que la unión izquierda sea única. Si el campo es numérico, puede ser útil usar el valor promedio Avg en la combinación izquierda.