Wie zu verwenden Unterabfrage in django?

Wann immer es ist eine schwierige Abfrage zu schreiben, mit Django ORM, habe ich zunächst versuchen, die Abfrage in psql(oder was auch immer client, den Sie verwenden). Die SQL, die Sie wollen, ist nicht dies:

SELECT * FROM (
  SELECT DISTINCT ON 
    "shop_purchase.customer_id" "shop_purchase.id" "shop_purchase.date" 
  FROM "shop_purchase" 
  ORDER BY "shop_purchase.customer_id" ASC, "shop_purchase.date" DESC;
  ) AS result 
ORDER BY date DESC;

In der obigen SQL, die SQL innere ist suchen distinct auf eine Kombination von (customer_id, id und Datum) und da die id eindeutig sein für alle, bekommst du alle Datensätze aus der Tabelle. Ich bin vorausgesetzt, id ist der Primärschlüssel als pro-übereinkommen.

Wenn Sie brauchen, um zu finden, dass der Letzte Kauf eines jeden Kunden, müssen Sie etwas tun, wie:

SELECT  "shop_purchase.customer_id", max("shop_purchase.date")
FROM shop_purchase
GROUP BY 1 

Aber das problem mit der obigen Abfrage ist, dass es wird Ihnen nur der name des Kunden und Datum. Über das wird nicht helfen Ihnen bei der Suche nach Aufzeichnungen, wenn Sie diese Ergebnisse nutzen, die in einer Unterabfrage.

Verwenden IN müssen Sie eine Liste von eindeutigen Parametern zur Identifizierung eines Datensatzes, z.B. id

Wenn in Ihren Unterlagen id ist ein serial-key, dann können Sie nutzen die Tatsache, dass das Letzte Datum wird die maximale id. Damit Ihre SQL wird:

SELECT  max("shop_purchase.id") 
FROM shop_purchase
GROUP BY "shop_purchase.customer_id";

Beachten Sie, dass ich behielt nur ein Feld (id) in der ausgewählten Klausel in einer Unterabfrage mit IN.

Das komplette SQL wird jetzt sein:

SELECT * 
FROM shop_customer 
WHERE "shop_customer.id" IN 
    (SELECT  max("shop_purchase.id") 
     FROM shop_purchase
     GROUP BY "shop_purchase.customer_id");

und mit der Django-ORM sieht es so aus:

(Purchase.objects.filter(
    id__in=Purchase.objects
                   .values('customer_id')
                   .annotate(latest=Max('id'))
                   .values_list('latest', flat=True)))

Hoffe, es hilft!

Dies ist mein Ansatz, wenn ich einen Teil der Daten (N Elemente) zusammen mit dem Django-Abfrage. Dieses ist zum Beispiel die Verwendung von PostgreSQL und handlich json_build_object() Funktion (Postgres 9.4+), aber die gleiche Weise können Sie eine andere Aggregatfunktion in anderen Datenbank-system. Bei älteren PostgreSQL-Versionen können Sie verwenden, Kombination von array_agg() und array_to_string() Funktionen.

Stellen Sie sich vor Sie haben Article und Comment Modelle und zusammen mit jedem Artikel in der Liste, die Sie auswählen möchten, die 3 letzten Kommentare (ändern LIMIT 3 zu passen Sie die Größe der Teilmenge oder ORDER BY c.id DESC ändern die Sortierung der Teilmenge).

qs = Article.objects.all()
qs = qs.extra(select = {
    'recent_comments': """
    SELECT
        json_build_object('comments',
            array_agg(
              json_build_object('id', id, 'user_id', user_id, 'body', body)
            )
        )
    FROM (
        SELECT
          c.id,
          c.user_id,
          c.body
        FROM app_comment c
        WHERE c.article_id = app_article.id
        ORDER BY c.id DESC
        LIMIT 3
    ) sub
    """
})

for article in qs:
    print(article.recent_comments)

# Output:
# {u'comments': [{u'user_id': 1, u'id': 3, u'body': u'foo'}, {u'user_id': 1, u'id': 2, u'body': u'bar'}, {u'user_id': 1, u'id': 1, u'body': u'joe'}]}
# ....