Django Jointure Externe Gauche

J'ai un site web où les utilisateurs peuvent voir une liste de films, et de créer des revues pour eux.

L'utilisateur doit être en mesure de voir la liste de tous les films. En outre, SI ils ont revu le film, ils devraient être en mesure de voir le score qu'ils ont donné. Si non, le film est simplement affiché sans le score.

Ils ne se soucient pas du tout sur les scores fournis par d'autres utilisateurs.

De considérer les éléments suivants 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")

Ce que j'ai essentiellement voulez, c'est ce

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

De considérer les éléments suivants test.py pour le contexte:

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)

Les DEUX tests doivent imprimer exactement la même sortie, cependant, que la première version crache le bon tableau des résultats:

raw 
--- 
1 
B Aucun 
C 3

l'orm au lieu de cela renvoie cette

orm 
--- 
1 
C 3

Toute tentative de joindre à dos le reste des sujets, ceux qui n'ont pas de commentaires de l'utilisateur "johnny", le résultat suivant:

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

Comment puis-je accomplir le simple comportement de la matière première requête avec l'ORM de Django?

edit: Ce genre de travaux, mais semble très pauvres:

sujets = Sujet.objets.filtre(record__utilisateur_id=1).values_list('nom', 'enregistrer__valeur') 
noned = Sujet.objets.exclure(record__utilisateur_id=1).values_list('nom') 
pour le sujet en chaîne(sujets, noned): 
...

edit: Cela fonctionne un peu mieux, mais toujours mauvais:

 sujets = Sujet.objets.filtre(record__utilisateur_id=1).annoter(valeur=F (record__valeur')) 
sujets |= Sujet.objets.exclure(pk__dans=les sujets)

orm 
--- 
1 
B 5 
C 3

Cette question semble très relative: stackoverflow.com/questions/6500066/...
Je suis maintenant à la recherche à RawSQL: docs.djangoproject.com/en/1.9/ref/models/expressions/...
quel est le problème avec Record.objects.get(user=mary, topic=A)?
il se complique pas faire ce que je veux. il ne me le les dossiers où Marie a écrit quelque chose, et le sujet est A. je veux que tous les sujets, et pas seulement Un, et pas seulement ceux pour lesquels Marie a écrit un examen.
plus de ressources: djangosnippets.org/snippets/236
Pour clarifier, basé sur l'un de vos commentaires, est-ce juste pour SQLite?
SQLite serait bien que c'est la valeur par défaut pour le test, mais je veux juste savoir la meilleure façon possible de rapprocher la coquette requête SQL, j'ai près du début de la question.
L'unicité de l'Enregistrement peuvent être plus explicites déclaré (que l'Utilisateur peut avoir qu'un seul Enregistrement pour le Sujet), par class Meta: unique_together = ("user", "topic"). C'est une condition fondamentale pour la question et pour l'ensemble des quatre solutions actuelles.
Dans le futur, vous serez en mesure d'annotation d'une sous-requête sur le queryset: github.com/django/django/pull/6478
hâte de

InformationsquelleAutor RodericDay | 2016-06-27

22

Tout d'abord, il n'y a pas un moyen de billets (atm Django 1.9.7) de disposer d'une représentation avec l'ORM de Django de la première requête que vous avez posté, exactement que vous le souhaitez; bien que, vous pouvez obtenir le même résultat souhaité avec quelque chose comme:
```
>>> 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)]
```
Ici le SQL généré pour la première requête:
```
>>> 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
```
Quelques notes
- N'hésitez pas à utiliser des requêtes, en particulier lorsque la performance est la chose la plus importante. En outre, parfois, il est un must, puisque vous ne pouvez pas obtenir le même résultat à l'aide de Django reinhardt, ORM; dans d'autres cas, vous pouvez, mais une fois tout en ayant propre et compréhensible code est plus important que la performance dans cette pièce de code.
- distinct avec des arguments de position est utilisé dans cette réponse, qui est disponible pour PostgreSQL seulement, l'atm. Dans les docs, vous pouvez en savoir plus sur les expressions conditionnelles.
- C'est vraiment bon, mais je vais tenir un peu au cas où il y a d'autres bonnes réponses. Les petits problèmes sont: distinct ne fonctionne pas sur SQLite, le besoin de l'étape distincte.
- mais la question est étiqueté comme mysql
- mon mauvais! J'ai juste pris l'auto-recommandé tags, sans assez de la pensée. "distinct" est pour postgres, cependant.
- +1. La seule objection est qu'un champ unique devrait être ajouté à la clause distinct distinct('id', 'name') sinon, une ligne peut être perdu si un deuxième objet avec le même nom est possible par accident.
- J'ai mis à jour la réponse avec quelques suggestions. Merci @hynekcer
- Il est intéressant de noter que la solution sans .order_by('id', 'name', 'f') est pas valide pour Marie. Si l'ordre est décroissant par f: .order_by('id', 'name', '-f') alors il n'est pas valide pour les deux Johnny et Marie. Pouvez-vous l'expliquer? Pensez-vous qu'il sera efficace pour les grosses tables ainsi que les premières requête solution?
- Vous pouvez trouver la réponse ici docs.djangoproject.com/en/1.9/ref/models/querysets/..., comme vous pouvez le voir il y, nous obtenons quelques lignes en double en raison de cette requête s'étend sur plusieurs tables, donc nous aurons besoin de les tailler en conséquence pour chaque entrée unique, avec order_by nous avons mis l'ordre de sorte que la taille s'exécute comme nous le souhaitons. Si vous comparez les deux requêtes, vous verrez les premières requête proposée par @RodericDay devrait être plus rapide.
- Je suis déchiré entre cette réponse et @de Todor, mais celui-ci semble le mieux aimé et était beaucoup plus tôt, et ce n'est évidemment définitive, de sorte qu'il devient le bounty.
- Merci @RodericDay, je pense que toutes les réponses ont une grande valeur, chacun d'eux a ses propres particularités et des contributions. Je suis sûr que vous (et tous les utilisateurs qui lisent ce thread) aura un bon pool d'idées pour faire face à la left join problème de Django. Merci pour la bonne cause!
InformationsquelleAutor trinchet

Ce que j'ai essentiellement voulez, c'est ce

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

...ou, peut-être que c'est l'équivalent qui permet d'éviter une sous-requête...

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

Je veux savoir comment le faire efficacement, ou, si cela est impossible, une explication de pourquoi c'est impossible...

Sauf si vous utiliser des requêtes, il est impossible avec l'ORM de Django, et voici pourquoi.

QuerySet objets (django.db.models.query.QuerySet) ont un query attribut (django.db.models.sql.query.Query) qui est une représentation de la requête réelle qui sera effectuée. Ces Query objets utile d'avoir un __str__ méthode, de sorte que vous pouvez l'imprimer pour voir ce que c'est.

Nous allons commencer avec 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

...ce qui n'est évidemment pas d'aller travailler, en raison de la INNER JOIN.

De prendre un regard plus profond à l'intérieur de la Query objet, il y a un alias_map attribut qui détermine ce que les jointures de table sera effectué...

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

Noter que Django prend uniquement en charge les deux join_types, INNER JOIN et LEFT OUTER JOIN.

Maintenant, nous peut utiliser le Query de l'objet promote_joins méthodes à utiliser un LEFT OUTER JOIN sur le bar_record table...

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

...qui va modifier la requête pour...

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

...cependant, cela n'est toujours pas utiliser, depuis la jointure sera toujours à la hauteur d'une ligne, même si elle n'appartient pas à l'utilisateur, et le WHERE clause de filtrer.

À l'aide de values_list() automatiquement les influences de la 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

...mais finalement souffre du même problème.

Il n'y a, malheureusement, une limitation fondamentale dans les jointures généré par l'ORM, en ce qu'ils ne peuvent être de la forme...

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

...donc il n'y a vraiment aucun moyen pour atteindre vos SQL, autres que l'utilisation d'une crue de la requête.

Sûr, vous pouvez pirater autour avec des choses comme annotate() et extra(), mais ils vont probablement générer des requêtes qui sont beaucoup moins performant, et sans doute pas plus lisible que SQL brut.

...et une alternative suggérée.

Personnellement, j'aimerais utiliser le raw requête...

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

...ce qui est assez simple pour être compatible avec l'ensemble de Django est pris en charge backends.

InformationsquelleAutor Aya

C'est comment j'allais le faire. Deux requêtes, pas un seul:

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

L'avantage est que vous obtenez l'ensemble Record objet. Donc envisager une situation où vous voulez non seulement de montrer la value, mais le time-stamp aussi.

Juste pour le record, je veux montrer une plus solution à l'aide de .extra. Je suis impressionné personne n'a le mentionner, parce qu'il doit produire la meilleure performance possible.

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

Ces deux solutions peuvent être abstrait dans un personnalisé TopicQuerySet classe de ré-utilisabilité.

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

InformationsquelleAutor Todor

7

Ce plus de solution universelle inspiré par trinchet réponse travaille également avec d'autres bases de données:
```
>>> qs = Topic.objects.annotate(
...         f=Max(Case(When(record__user=johnny, then=F('record__value'))))
... )
```
les données de l'exemple
```
>>> print(qs.values_list('name', 'f'))
[(u'A', 1), (u'B', None), (u'C', 3)]
```
vérifier la requête
```
>>> 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
```
Avantages (par rapport à l'original solutions)
- Il fonctionne également avec SQLite.
- La requête peut être facilement filtrés ou triés, n'importe comment.
- Pas de type cast output_field est nécessaire.
- Les méthodes values ou values_list(*field_names) sont utiles pour une plus simple GROUP BY, mais ils ne sont pas nécessaires.
La gauche de la jointure peut être rendu plus lisible par l'écriture d'une fonction:
```
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')
```
Plus de champs à partir de l'Enregistrement peut être ajouté par anotate méthode résultats de cette façon, avec de belles mnémonique noms.

Je suis d'accord avec d'autres auteurs qu'elle peut être optimisée, mais la lisibilité compte.

MODIFIER: Le même résultat en vient, si la fonction d'agrégation Max est remplacé par Min. Min et Max ignorer les valeurs NULLES et peut être utilisé sur n'importe quel type, comme par exemple pour les chaînes. L'agrégation est utile si la jointure gauche n'est pas garanti d'être unique. Si le champ est numérique qu'il peut être utile d'utiliser la valeur moyenne Avg sur la gauche de la rejoindre.

InformationsquelleAutor hynekcer
5

Premières Requêtes.
```
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
            ''')
```
Vous semblez connaître la réponse vous-même. Il n'y a rien de mal à utiliser le raw d'une requête lorsque vous ne pouvez pas obtenir l'ORM requête se comportent exactement de la façon dont vous le souhaitez.

Le principal inconvénient avec les premières requêtes, c'est qu'ils ne sont pas mis en cache comme ORM requêtes. Cela signifie que si vous parcourir les premières queryset deux fois, la requête sera répété. Une autre est que vous ne pouvez pas appeler .count() sur celui-ci.

Null Clés Étrangères

Vous pouvez forcer l'ORM à l'utilisation d'une JOINTURE EXTERNE GAUCHE PAR la mise en null=True dans les clés étrangères. Faire cela avec des tables comme ils sont.
```
print Record.objects.filter(user_id=8).select_related('topic').query
```
Le résultat est
```
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
```
Maintenant définie, null=True et effectuer la même ORM requête comme ci-dessus. Le résultat est
```
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
```
Note de la façon dont la requête a brusquement changé de LEFT OUTER JOIN. Mais nous ne sommes pas encore sortis du bois, car l'ordre des tables doivent être inversés! Donc, sauf si vous pouvez restructurer vos modèles, un ORM JOINTURE EXTERNE GAUCHE ne peut pas être tout à fait possible sans chaînage ou de l'UNION de deux de qui vous avez essayé déjà.
- Cette jointure gauche n'est pas un problème et vous avez besoin d'un droit de participer à cette requête maintenant. Chaque Enregistrement a un Sujet, mais pas chaque Sujet a un Record.
- exactement @hynekcer , c'est ce que moi aussi, j'essayais de souligner
- (Je suis désolé. Il y a vingt ans mon chef qu'il pouvait lire en espagnol, s'il y a assez de formules. 🙂 Maintenant, j'ai lu uniquement les formules.) Hier, j'ai fait un grep par django.db pour une jointure gauche, jointure externe et interne liée aux structures o tre requête. Le résultat en est que deux circonstances peuvent provoquer l'EXTÉRIEUR (gauche/droite) REJOIGNEZ. C'est une clé étrangère avec null=True ou une condition OU à cause d'une condition foreignkey_a.name='A' OR foreignkey_b.name='B' pourrait donner des résultats non valides si les tables sont jointes par une jointure interne. La solution par @trinchet est basé sur le OU logique de type.
InformationsquelleAutor e4c5

Vous devez vous connecter pour publier un commentaire.

Quelques notes

Premières Requêtes.

Null Clés Étrangères