Multiple Datensätze via Join aus Array abfragen

[Kampfgummibaerlie]

Ich habe in einer Tabelle "Film" die Titel in einer Spalte, in einer anderen (einer Array-Spalte) die Eigenschaften.

Ich habe eine kleine Homepage gebastelt, wo man aus einer Dropdown-Liste den Titel auswählen kann, wenn sich der Titel ändert,
werden darunter die Attribute angezeigt.

Hier der Code für die Ansicht, aus welcher ich die Attribute darstelle:

 SELECT film.id,
    film.titel,
    attribut.name
   FROM film
     CROSS JOIN attribut
  WHERE film.attribut <@ (ARRAY( SELECT "attribute".id
           FROM attribut AS "attribute"))
  ORDER BY attribut.id;

Hier der Code, um aus der Ansicht "einen" Datensatz mit mehreren Zeilen (je Attribut eine)

select * from film_attribut where id = $1;

Wobei $1 hier der ID eines Films entsprechen muss.

Warum dieser Post:
1.: Vielleicht kann mich jemand ausbessern?
2.: Vielleicht relevant für andere?
3.: Weil ich es toll finde, dass ich mal wieder auf etwas draufkomme

LG, Kampfgummibaerlie

 

[castorp]

Ich verstehe den Grund für den CROSS JOIN mit dem Aggregat nicht. Für mich sieht das so aus, als würde ein normaler JOIN das Gleiche erreichen (und vermutlich schneller):

SELECT film.id,
       film.titel,
       attribut.name
FROM film
 join attribut on attribut.id = film.attribut

Ein ORDER BY in einer View finde ich eher unpraktisch. Wenn dann beim Abfragen der View auch ein ORDER BY verwendet wird, wird das ganze zweimal sortiert (Postgres ist nicht schlau genug, das in der View "versteckte" ORDER BY zu ignorieren in dem Fall

 

[Kampfgummibaerlie]

Einen join hatte ich auch probiert, das Problem ist nur, man kann nicht 2 verschiedene Datentypen joinen:

ERROR:  operator does not exist: integer = integer[]LINE 5:  join attribut on attribut.id = film.attribut                                    
HINT:  No operator matches the given name and argument types.
You might need to add explicit type casts. SQL state: 42883Character: 97

attribut.id = Integer
film.attribut = Integer[]

Nachdem ich nicht auf Anhieb einen Int in einen Int[] casten konnte, habe ich das Problem so gelöst ^^

EDIT:
Warum das Order By?

Damit bei allen Filmen die Reihenfolge der Attribute gleich ist.
Ich möchte keine Views bauen, wo ich nachher noch was ändern muss.

 

[castorp]

join attribut on attribut.id = any(film.attribut)

 

[Kampfgummibaerlie]

Castorops Idee: 0,0121 Sekunden
Meine Idee: 0,0310 Sekunden

deutlich schneller ist deine Methode das mit any kenne ich noch nicht allzugut... :/

 

[castorp]

Der Unterschied in der Laufzeit dürfte immer größer werden, je größer die Tabellen werden.

 

[ukulele]

Kann man in PG ORDER BY in einer View verwenden? MSSQL lässt das z.B. nicht zu. Ist eventuell aus dem Test-Code.

Arrays sind mir auch eher fremd Was ist der Vorteil gegenüber z.B. JSON?

 

[Kampfgummibaerlie]

Kann man in PG ORDER BY in einer View verwenden?

ja, mit JSON kenne ich mich wiederum nicht ganz aus ^^

 

[castorp]

Kann man in PG ORDER BY in einer View verwenden? MSSQL lässt das z.B. nicht zu. Ist eventuell aus dem Test-Code.

Ja, Postgres (und auch Oracle) lassen das zu. Leider ist Postgres (im Gegensatz zu Oracle) nicht schlau genug das überflüssige ORDER BY zu ignorieren wenn man bei der Verwendung der View auch ein ORDER BY verwendet (das Ergebnis wird dann zweimal sortiert)

Arrays sind mir auch eher fremd Was ist der Vorteil gegenüber z.B. JSON?

Es gibt sicherlich große Überlappung in der Funktionalität.Die Typprüfung ist z.B. ein Vorteil bei "nativen" Arrays. In einem int[] kann ich nunmal keine Zeichen oder Datumswerte speichern. In einem JSON Array ist es extrem aufwändig (und ineffizient) das sicher zu stellen.

 

[ukulele]

Dann ist das Array intern vermutlich wie eine Tabelle implementiert?

 

[castorp]

Nein, das ist ein Wert in einer Spalte (ähnlich einem JSON Wert in einer Spalte)