Blacklist mit SQL

Hallo zusammen,

ich habe ein kleines Problem, bei dem ich mich über eine Idee von Euch freuen würde. Es hört sich zwar auf den ersten Blick einfach an, scheint es doch in sich zu haben.

Folgendes Szenario:
Ich habe eine Liste an Tags, die ich meiner Anfrage übergeben möchte, z.B.

  • Jacke
  • Hose
  • Pullover
  • Shirt

Diese Liste möchte ich mit einer Blacklist vergleichen, die in einer Tabelle abgelegt ist. In der Tabelle würde z.B. stehen:

  • Jac*
  • Ho?e
  • Pull*

Der Vergleich soll mir dann entweder die erlaubten Tags zurückgeben (alternativ auch die verbotenen, dann muss man da halt noch mal drüberiterieren). Also in diesem Fall nur:

  • Shirt (darauf matched kein Blacklisteintrag)

Der Punkt ist, dass ich gerne Wildcards unterstützen möchte. Wie würdet ihr das realisieren? Ich könnte natürlich eine Stored Procedure schreiben, die aus allen Wildcards einen regulären Ausdruck generiert, aber das erscheint mir auch nicht performant. Das muss doch einfacher gehen, oder?

Würde mich freuen, wenn jemand eine kreative Idee hätte :smiley:

Viele Grüße,
az_hf0

PS: In der Realität ist das eine Tagcloud mit gestemmten Begriffen aus dem Bereich Health Sciences, von denen manche irrelevant sind.

Grob gesagt würde ich das jetzt mal so versuchen:

SELECT taglist.tag FROM taglist LEFT JOIN blacklist ON taglist.tag = backlist.tag WHERE blacklist.tag IS NULL

Alternativ würde es denk ich auch mit taglist NOT IN(SELECT * FROM blacklist)

Hallo Progs,

das Matching über den Join würde mir die irrelevanten Tags so aber nicht entfernen, denn in der Blacklist-Tabelle sind ja Einträge mit Wildcards enthalten. Jemand noch eine Idee?

Viele Grüße,
az_hf0

Du wirst um reguläre Ausdrücke nicht herumkommen.

Um mal zu zeigen, wie es gehen könnte:

test=*# select * from sachen;
    s
----------
 jacke
 hose
 pullover
 shirt
(4 Zeilen)

Zeit: 0,190 ms
test=*# select * from sachen_suche ;
   r
--------
 jac.*
 ho.e
 pull.*
(3 Zeilen)

Zeit: 0,184 ms
test=*# select a.* from sachen a left join sachen_suche b on (a.s ~ b.r) where b.r is null;
   s
-------
 shirt
(1 Zeile)

Andreas

Danke Andreas, das ist wirklich ein guter Tip. Genau so hatte ich mir das vorgestellt, mir ist nur der ~-Operator nicht mehr eingefallen.

Ich würde das noch lieber realisieren, ohne meine paar Sucheinträge vorher in die Tabelle “sachen” zu schreiben zu müssen. In meinem Fall müsste “sachen” sonst wohl eine temporäre Tabelle sein, worunter die Performance vermutlich etwas leiden würde.

Weißt du, ob man direkt in einem Aufruf eine Menge in Tabellenform ranjoinen kann, die man on-the-fly übergibt?

Viele Grüße,
az_hf0

Vielleicht als ARRAY und daraus on-the-fly eine Tabelle machen.

Andreas

Ich habe jetzt den Tip von Andreas gut verwenden können und das Problem so gelöst:

CREATE TABLE literature_tagcloud_excludes
(
  excluded_item_id serial NOT NULL,
  excluded_tag text NOT NULL,
  CONSTRAINT pkey_literature_tagcloud_excludes PRIMARY KEY (excluded_item_id),
  CONSTRAINT unique_literature_tagcloud_excludes_excluded_tag UNIQUE (excluded_tag)
);

Die Anfrage wird dann so zusammengebaut:

SELECT
        tag.*
FROM 
        explode_array(
                array['Hose', 'Pullover', 'Jacke', 'T-Shirt']
        ) AS tag
LEFT JOIN 
        literature_tagcloud_excludes lte
ON 
        (tag ~* lte.excluded_tag) 
WHERE 
        lte.excluded_tag IS NULL;

Dabei wird eine Funktion verwendet, die aus einem übergebenen Array eine joinbare Tabelle erzeugt.

CREATE OR REPLACE FUNCTION explode_array(in_array anyarray)
  RETURNS SETOF anyelement AS
$BODY$
    SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

Bei {“Hos*”, “Pull*”, “Ja.ke”} in der Tabelle literature_tagcloud_excludes liefert die Anfrage dann das Ergebnis “T-Shirt”.

Danke für Eure Hilfe,
az_hf0