Mit UNNEST mit einem JOIN

Ich möchte in der Lage sein zu verwenden unnest() Funktion in PostgreSQL in eine komplizierte SQL-Abfrage, die viele JOINs. Hier ist der Beispiel-query:

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id
FROM mentions
INNER JOIN taggings ON taggings.mention_id = mentions.id
INNER JOIN tags ON tags.id = taggings.tag_id
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3
GROUP BY tags.parent_id

Will ich beseitigen, den taggings Tabelle hier, weil meine mentions Tisch hat eine integer-array Feld mit dem Namen " Taglisten, das aus allen verknüpften tag-ids von mentions.

Versuchte ich Folgendes:

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id 
FROM mentions 
INNER JOIN tags ON tags.id IN (SELECT unnest(taglist))
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3 
GROUP BY tags.parent_id

Funktioniert es, aber bringt andere Ergebnisse als die erste Abfrage.

So, was ich will zu tun ist, um das Ergebnis der SELECT unnest(taglist) im JOIN Abfrage zum Ausgleich für die taggings Tabelle.

Wie kann ich das tun?

UPDATE: taglist ist die gleiche Kulisse wie die jeweilige Liste von tag-ids zu nennen.

mentions table has an integer array field named taglist that includes tag ids of mention. Bitte definieren Sie "schließt". Ist taglist genau den gleichen Satz wie die jeweilige Liste von tag-ids zu nennen?
Ja, taglist ist die gleiche Kulisse wie die jeweilige Liste von tag-ids zu nennen. Ich aktualisiere meine Frage.

InformationsquelleAutor | 2013-04-17

5

Technisch gesehen, wird Ihre Abfrage könnte funktionieren wie diese (nicht ganz sicher über das Ziel dieser Abfrage):
```
SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT  unnest(m.taglist) AS tag_id
    FROM    mentions m
    WHERE   m.search_id = 3
    AND     9 = ANY (m.taglist)
    ) m 
JOIN   tags t  USING (tag_id) -- assumes tag.tag_id!
GROUP  BY t.parent_id;
```
Jedoch, es scheint mir du gehst in die falsche Richtung hier. Normalerweise würde man entfernen redundant array taglist und halten Sie die normalisierte Datenbank-schema. Dann ist dein original Abfrage sollte auch dazu dienen, nur verkürzt sich die syntax mit-Aliase:
```
SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM   mentions m
JOIN   taggings mt ON mt.mention_id = m.id
JOIN   tags     t  ON t.id = mt.tag_id
WHERE  9 = ANY (m.taglist)
AND    m.search_id = 3
GROUP  BY t.parent_id;
```
Entwirren das Geheimnis

<rant>
Die Ursache für Ihre "unterschiedlichen Ergebnissen" der unglückliche Namenskonvention, dass einige intellektuell herausgefordert ORMs verhängen, Leute.

Ich spreche von id als Spaltenname. Verwenden Sie niemals diese anti-pattern in einer Datenbank mit mehr als einer Tabelle. Recht, das bedeutet, dass im Grunde alle Datenbank. Sobald Sie sich ein Haufen von Tabellen (das ist, was Sie tun in einer Datenbank), die Sie am Ende mit einem Bündel von Spalten mit der Bezeichnung id. Völlig sinnlos.

Die ID-Spalte einer Tabelle mit dem Namen tag sollte tag_id (es sei denn, es gibt einen anderen beschreibenden Namen). Nie id.
</rant>

Ihre Abfrage versehentlich zählt tags statt mentions:
```
SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT unnest(m.taglist) AS id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t USING (id)
GROUP  BY t.parent_id;
```
Es sollte so funktionieren:
```
SELECT 25 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id
FROM  (
    SELECT m.id, unnest(m.taglist) AS tag_id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t ON t.id =  m.tag_id
GROUP  BY t.parent_id;
```
Ich habe auch wieder die DISTINCT zu Ihrem count(), die verloren ging auf dem Weg in Ihre Abfrage.

Wenn ich versuche, führen Sie Ihre erste Abfrage, bekomme ich die folgende Fehlermeldung: ERROR: column "tag_id" specified in USING clause does not exist in right table
OK, ich änderte den code wie folgt und es funktioniert: SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id FROM ( SELECT unnest(m.taglist) AS id FROM mentions m WHERE m.search_id = 4 AND 25 = ANY (m.taglist) ) m JOIN tags t USING (id) GROUP BY t.parent_id; Aber wieder erzeugt andere Ergebnisse als die ursprüngliche Abfrage.
Ich fügte hinzu, eine Erklärung. Konnte nicht widerstehen, die schimpfen ..
Gute Erklärung. Vielen Dank für Ihre Zeit.

InformationsquelleAutor Erwin Brandstetter

Etwas wie dies funktionieren sollte:

...
tags t INNER JOIN
(SELECT UNNEST(taglist) as idd) a ON t.id = a.idd
...

InformationsquelleAutor Wojtas

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

Entwirren das Geheimnis