Zeilenweiser IN BETWEEN-Vergleich in einer einzigen SQL-Anweisung

Question

Gegeben sind die folgenden Tabellen:

CREATE TABLE tree (
    id serial NOT NULL,
    name character varying NOT NULL,
    type integer,
    lft integer NOT NULL,
    rgt integer NOT NULL )

CREATE TABLE item (
    id serial NOT NULL,
    name character varying NOT NULL,
    tree_id integer
    CONSTRAINT fk_tree FOREIGN KEY (tree_id) REFERENCES tree (id) )

Rubriken lft y rgt auf dem Tisch tree werden mit Hilfe des Algorithmus MPTT (Modified Preorder Tree Traversal) aufgefüllt.

Ist es möglich, alle Elemente, die an Baumknoten mit type=x angehängt sind, und alle ihre Nachkommen in einer einzeln abfragen?

Normalerweise würde ich das in zwei separaten Abfragen wie dieser tun:

SELECT lft, rgt FROM tree WHERE type = x;
/* assume it returns two rows: ((10, 20), (27, 30)) */

SELECT item.id, item.name FROM item JOIN tree ON (tree_id = tree.id)
WHERE ((lft >= 10 AND rgt <= 20) OR (lft >= 27 AND rgt <= 30);

Der Punkt ist, dass ich nur eine einzige SQL-Anweisung ausführen kann (in einer PostgreSQL-Datenbank, wenn es wichtig ist). Können Sie dies mit einer Art von Subquery tun?

Danke für jeden Hinweis.

Answer 1

SELECT  item.*
FROM    tree tm
JOIN    tree tc
ON      tc.lft >= tm.lft
        AND tc.rgt <= tm.rgt
JOIN    item
ON      item.tree_id = tc.id
WHERE   tm.type = x

Vielleicht sollten Sie das Eltern-Kind-Modell in Betracht ziehen, das viel einfacher zu verwalten ist.

Siehe diese Artikel in meinem Blog zur Implementierung in PostgreSQL 8.3 und darunter:

• Hierarchische Abfragen in PostgreSQL

und in PostgreSQL 8.4 :

• PostgreSQL 8.4 : Erhaltung der Ordnung bei hierarchischen Abfragen

Answer 2

Nun, wenn Sie Probleme mit dem Schreiben von Abfragen für eine bestimmte Datenstruktur haben, sollten Sie vielleicht eine andere Datenstruktur verwenden. Verschachtelte Mengen (wie Sie sie hier gezeigt haben) sehen cool aus, sind aber ein großes Problem beim Schreiben von Abfragen für sie.

Vielleicht könnten Sie etwas verwenden wie ce (Offenlegung: es ist ein Beitrag in meinem Blog).