2 Tabellen in einen View

Hallo!

Ich habe folgende Tabellenstruktur:

Tabelle A (tbla)
pk_id | other_ids
1 | 1, 2, 3,…,n
2 | 1,3
3 | 3,7

Tabelle B (tblb)
pk_id | name
1 | nameA
2 | nameB
3 | nameC

und möchte die beiden in einen View zusammenführen der so ausschaun soll:

View
tbla_pk_id | names
1 | nameA,nameB,nameC,…,nameN
2 | nameA,nameC
3 | nameC,nameG

Ich habe leider keine Ahnung wie die query aussehe soll…

Select tbla.pk_id as tbla_pk_id, tblb.name From tbla Join tblb On tbla.other_ids = tblb.pk_id

Vielleicht kann mir da wer weiterhelfen, wäre super!

Zuerst einmal trittst Du Deine erste Tabelle in die Tonne. Dann erstellt Du diese neu, aber anders:

test=# create table a (id int, other_id int, primary key (id, other_id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE                                                                         
test=*# create table b (pk_id int, name text);
CREATE TABLE                                  
test=*# commit;                               
COMMIT                                        
test=# copy a from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    1                                             
>> 1    2                                             
>> 1    3
>> 2    1
>> 2    3
>> 3    3
>> 3    7
>> \.
test=*# copy b from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    name1
>> 2    name2
>> 3    name3
>> 4    name4
>> 5    name5
>> 6    name6
>> 7    name7
>> \.
test=*# commit;
COMMIT

Dann findest Du in der Doku array_accum. Der Fernsehkoch würde nun sagen: ‘Ich hab da mal was vorbereitet…’

test=# CREATE AGGREGATE array_accum (anyelement)
test-# (
test(#     sfunc = array_append,
test(#     stype = anyarray,
test(#     initcond = '{}'
test(# );
CREATE AGGREGATE

Nun erfreust Du Dich an folgender Abfrage:

test=*# select a.id, array_to_string(array_accum(b.name), ', ') from a left join b on (a.other_id=b.pk_id) group by 1;
 id |   array_to_string
----+---------------------
  2 | name1, name3
  3 | name3, name7
  1 | name1, name2, name3
(3 rows)

Andreas

Hallo Andreas!

Danke für die Hilfe, das Problem ist nur, dass ich die erste Tabelle nicht ‘in die Tonne’ schmeißen kann, weil da schon zu viele Datensätze vorhanden sind.
Könnte man nicht die Spalte ‘other_ids’ als ein array behandeln und dann in der Query jedes einzelne Element des arrays (in dem die ID Verweise auf Tabelle B sind) mit den pk_ids von Tabelle B abfragen?

Im Moment löse ich das Problem anders, nur ist es denk ich nicht so performant bei größeren Datenmengen:
Tabelle A hat in dieser Problemlösung keine ‘other_ids’ Spalte.
Ich verwende stattdessen eine dritte Tabelle C die so aufgebaut ist:

pk_tblc_id | tbla_id | tblb_id
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 2 | 3

das ganze wird in einen View vwa zusammengefasst:

SELECT tblc.tbla_id, tblc.tblb_id, tblb.name 
FROM tblc 
LEFT JOIN tblb ON tblb.pk_id = tblc.tblb_id;

Der View vwa sieht dann so aus:
tbla_id | tblb_id | name
1 | 1 | nameA
1 | 2 | nameB
1 | 3 | nameC
2 | 1 | nameA
2 | 3 | nameC

Um jetzt die tblb_id und tblb.name bei gleicher tbla_id in eine Zeile zu schreiben, kommt ein zweiter view ins spiel, vwb:

SELECT vwa_a.tbla_id, array_to_string(ARRAY( SELECT vwa.name FROM vwa
WHERE vwa.tbla_id = vwa_a.tbla_id), ',') AS name
FROM vwa vwa_a
GROUP BY vwa_a.tbla_id;

vwb sieht dann so aus:
tbla_id | name
1 | nameA,nameB,nameC
2 | nameA,nameC


Die Frage ist, ist die oben beschriebene Methode die einzige Möglichkeit meinen Wunsch zu realisieren OHNE die Tabelle A zu ändern bzw. ‘in die Tonne’ zu werfen oder gibts noch andere alternativen?

Sicher geht auch das.

test=# select * from doof;
  other_id
------------
 1, 2, 4, 7
(1 row)

test=*# CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT LANGUAGE SQL AS $$SELECT $1[i] FROM generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
CREATE FUNCTION
test=*# select unnest(string_to_array(other_id, ', ')) from doof;
 unnest
--------
 1
 2
 4
 7
(4 rows)

Schön ist was anderes.

PS.: mit Danke an David Fetter für das unnest().

Andreas

Super danke Andreas!

Das mit dem Array war doch nicht nötig, habs jetzt mit der ‘array_accum’ Funktion aus deiner ersten Antwort gemacht.
Eine Frage noch, kann ich in der aggregate function gleich die Daten nach den Namen aufsteigend sortieren?
Ein Äquivalent zu ‘ORDER BY name ASC’ wäre toll!

Ich hab versucht die Theorie zu den aggregate functions zu verstehen, leider mit nicht so viel erfolg. Ich weiß, dass es ein ‘sortop’ gibt aber damit hab ich nichts weiter anfangen können.
Gibts da eine Möglichkeit?

Weiß nicht, ob Dir das was hilft:

test=*# select a.id, array_to_string(array_accum(b.name), ', ') from a left join b on (a.other_id=b.pk_id) group by 1;
 id |   array_to_string
----+---------------------
  2 | name1, name3
  3 | name3, name7
  1 | name1, name2, name3
(3 rows)

test=*# select a.id, array_to_string(array_accum(b.name), ', ') from a left join (select * from b order by 1 desc)b on (a.other_id=b.pk_id) group by 1;
 id |   array_to_string
----+---------------------
  2 | name3, name1
  3 | name7, name3
  1 | name3, name2, name1
(3 rows)

Andreas

Nein ich meine folgendes:
Ich wills z.b. nicht so sortiert haben:

test=*# select a.id, array_to_string(array_accum(b.name), ', ') from a left join b on (a.other_id=b.pk_id) group by 1;
 id |   array_to_string
----+---------------------
  1 | f, b
  2 | h, r, c
  3 | a, g, b

sondern so:

 id |   array_to_string
----+---------------------
  1 | b, f
  2 | c, h, r
  3 | a, b, g

Verständlicher?
Ohne subselect?

Dann doch so, wie gezeigt. Mit Subselect, welches Dir das, was im Array landet, vorher sortiert.

Andreas

Ich meinte eher, ob es denn auch ohne dem subselect funktioniert?
subselects sind doch nicht besonders performant oder?

Wenns nicht anders geht, dann passts schon, danke für die Hilfe!

Wenn hier etwas nicht performant ist, dann Dein Datenmodell. Das sagte ich aber (indirekt) schon in meiner ersten Antwort.

Andreas