Hallo,
ich speichere Wörter in einer Tabelle mit einer WortId zusammen. Im Prinzip das gleiche was Computer mit Zeichensätzen machen also eine Nummer repräsentiert das Zeichen. Bei mir das ganze halt mit Wörtern.
create table Woerter
(
WortId bigserial primary key,
Wort text unique
);
In einer zweiten Tabelle speichere ich Erklärungen zu den Wörtern. Hier wird der Text in einem Array aus WortIds dargestellt.
create table Erklaerung
(
WortId bigint references Woerter (WortId) on delete cascade,
Erklaerung bigint[]
);
Jetzt habe ich versucht mit einem select die Erkläerung wieder in einen Text aus Wörtern umzuwandeln. Das funktioniert leider nur fast. Die Ausgabe des select wird leider nach der WortId sortiert. Deshalb ist der Text dann schwer zu verstehen.
select * from woerter where wortid in (select unnest (erklaerung) as Id from erklaerung where wortid = 50);
So sieht das Array aus:
wortid | erklaerung |
--------+-----------------------------------+
50 | {12,49,5,11,53,41,54} |
So sieht der Output des selects aus:
wortid | wort
--------+-----------+
5 | mir |
11 | die |
12 | du |
41 | daten |
49 | sollst |
53 | genannten |
54 | suchen |
Jetzt fehlt mir irgendwie die Idee wie ich die Sortierung so hin bekomme das die Wörter in der Reihenfolge stehen wie im Array die WortIds waren. Ich könnte natürlich das manuell im Programm machen aber ich fände es hübscher wenn es die Datenbank gleich richtig liefert.
Viele Grüße
Alexander
test=*# select * from woerter ;
id | wort
----+-----------
5 | mir
11 | die
12 | du
41 | daten
49 | sollst
53 | genannten
54 | suchen
50 | test
(8 rows)
test=*# select * from erklaerung ;
wort_id | erklaerung
---------+-----------------------
50 | {12,49,5,11,53,41,54}
(1 row)
test=*# with id as (select unnest (erklaerung) as Id from erklaerung where wort_id = 50) select * from id left join woerter on id.id=woerter.id;
id | id | wort
----+----+-----------
12 | 12 | du
49 | 49 | sollst
5 | 5 | mir
11 | 11 | die
53 | 53 | genannten
41 | 41 | daten
54 | 54 | suchen
(7 rows)
test=*#
Danke funktioniert.
Liegt das an der Reihenfolge?
Ich hatte ja die Daten aus der Woerter Tabelle gesucht. Bei Deiner Version nimmst Du das Id Array und sortierst die Woerter dazu.
Das ganze ist a bissl blöd, ich kann/will auch nicht garantieren, daß das stabil funktioniert. Hintergrund: Datenbanken arbeiten mit Mengen, die per se unsortiert sind, und es ist der DB frei, in Abfragen ohne ORDER BY die Daten in absolut beliebiger Ordnung zu liefern. Es kann sogar passieren, daß 2 nacheinander laufende Abfragen auf nicht geänderte Daten diese in untersch. Reihenfolge bringen, Grund kann u.a. ein Feature namens ‘synchronized parallel scans’ sein.
Also, um es klar zu sagen: das kann u.U. in späteren Versionen von PG nicht mehr garantiert sein, daß das funktioniert. Vielleicht überlegst Du Dir eine andere Lösung, die nicht auf der Annahme basiert, daß irgend etwas in irgend einer Reihenfolge ist. Die Reihenfolge der Elemente im Array mag fest sein und bleiben, vielleicht eine Funktion, die durch das Array geht und dazu noch die Position im Array mitliefert, als Tabelle. Diese extra Spalte kann dann zum sortieren dienen.
Die Position der Array Element kannst Du über “WITH ORDINALITY” beim unnest() bekommen. Das geht aber nur wenn man die Funktion im FROM verwendet:
select x.id, w.wort
from erklaerung e
cross join unnest(e.erklaerung) with ordinality as x(id, nr)
join woerter w on w.id = x.id
where e.wort_id = 50
order by x.nr;
Online Beispiel: https://rextester.com/FCYBH91833
Ja das Problem kenne ich das nichts sortiert ist wenn man es nicht extra sortiert. Das array ist aber ein normaler Datentyp der darf die Reihenfolge nicht einfach ändern. Man muss aber das Array halt in eine Menge umwandeln und dann mit einer anderen Verbinden. Da ist viel Raum für etwas das schief gehen kann. Ist mir schon klar. Ob der unnest die Reihenfolge garantiert oder nicht konnte ich nichts dazu finden.
Das mit dem unnest … with ordinality habe ich gerade versucht zum laufen zu bekommen. Das ist mir beim Suchen in der PostgreSQL Dokumentation aufgefallen. Castorp war da schneller mit einer Funktionierenden Version. Sieht aber schon recht Aufwendig aus.
Momentan erscheint mir die Lösung das vom Anwendungsprogramm aus zu machen am einfachsten und übersichtlichsten. Wollte ich zwar erst nicht. Das Array ist leicht in einzelne Werte zu zerlegen und dann die Wörter aus der Tabelle nachlesen. Bin mir nicht sicher …
In der Anwendung würde ich das nur machen, wenn Du die Daten satzweise (einzeln) abrufst, nicht für Massenverarbeitung.
Und was solls, wenn es kompliziert aussieht? Du musst ja nicht persönlich die Bits hin und her schieben.
Ist schon eher Massenverarbeitung.
Aufwändig schrieb ich wegen Laufzeit. Ich probiere gerade ein bisschen damit herum was von den drei Möglichkeiten wenig Rechenzeit kostet. Das ist aber nicht so einfach weil ja nicht nur die Datenbank daran beteiligt ist. So wie es aussieht ist es gar nicht so Aufwändig wie ich dachte. Optische Täuschung … 
Naja, das Problem ist halt, dass Dein Modell nicht normalisiert ist, und Du deswegen jedes mal das Array in eine Liste verwandeln musst (also quasi die Daten “on-the-fly” normalisierst). Würdest Du eine klassische m:n mapping Tabelle verwenden, dann wäre das ein einfacher join.
Ich weiß das das so ist aber es wird nicht wirklich besser wenn man jede kleine 1:n Beziehung in eigene Tabellen verlegt. Zumal ich die Umsetzung der WortId in echtes Wort nur brauche wenn die Anwendung Informationen zum User ausgibt. Die Texte werden sonst in numerischer Form verarbeitet und dann habe ich mit einem Zugriff gleich den ganzen Text und muss das nicht erst aus mehreren Tabellen zusammen suchen. Das wird echt nicht schneller durch mehr Tabellen weil es viele eher kleine Texte sind die in einer eigenen Tabelle dann Millionen Zuordnungen ergeben.
Ich mach das jetzt mal so wie Du es vorgeschlagen hast und schau wie es sich entwickelt. Die schnellste Version ist die von akretschmer die er aus meiner leicht falschen Version gebaut hat. Ich habe einen kleinen automatischen Test in das Programm integriert um mal zu sehen ob es vorkommt das die Reihenfolge durcheinander gerät. Ich glaube ja eher das es nicht passiert. In der Entwicklung kann man das ja leicht machen und bis zu ersten fertigen Version wird sicher noch einige Zeit vergehen. Das nächste PostgreSQL Upgrade steht auch schon an.
ich geb castorp hier Recht. Wenn Du das schon mit den Arrays machst, dann solltest Du zumindest die von ihm gezeigte Lösung mit ordinality nutzen, sonst fliegt Dir das irgendwann um die Ohren.
Du kennst das Datenbankmodell und die Anwendung im Detail?
Wie kommst Du darauf das ich zusätzliche Tabellen um jeden Preis vermeiden will?
Relationale Datenbanken können ganz bestimmt Anwendungsfälle sehr gut lösen aber eben nicht alle.
In der Tat gibt es anders Aufgebaute Datenbanken und zwar genau aus dem Grund das Relationale Datenbanken nicht alle Probleme gut lösen können. Das ist eine recht alte Erkenntnis der Datenbanken Produzenten allen voran IBM die mit DB2 eine der ersten hatten. Bin mir nicht sicher aber ich glaube die haben die auch erfunden.
All Deine schöne Relationale Theorie fliegt Dir um die Ohren wenn sie an den Gesetzen der Physik zerschellt. Das Thema ist z.B Festplatten und Speicher IO und die Probleme folgen keiner Relationalen Theorie. Fakt ist: mehr zugriffe = mehr langsam. Du kannst das gerne mal ausprobieren eine eigene Mapping Tabelle ist langsamer deutlich langsamer.
Die zwei Versionen liefern das gleiche Ergebnis aber auf unterschiedlichem Weg. Der unterschiedliche Weg wird wohl verantwortlich sein das es nicht die gleiche Laufzeit hat. Alleine schon der nötig Sort kostet nicht wenig Zeit nur mal so als Beispiel für die Unterschiede. Ein Sort kostet auch Zeit wenn er gar nichts zum sortieren findet.
@akretschmer: steht doch schon in dem Post oben das ich die benutze. Ich lasse nur die andere mal mit laufen in der Entwicklung um zu sehen was es in der Praxis tut. Ist ja nicht wirklich Zeit kritisch eine Ausgabe für User. Ich will aber trotzdem wenigstens halbwegs schnelle Lösungen. Green IT ist wenn die CPU sich langweilt und trotzdem das Ergebnis am Bildschirm steht …