4
votes

Requête parent / enfant récursive PostgreSQL

J'ai du mal à élaborer la documentation PostgreSQL pour les requêtes récursives et je me demande si quelqu'un pourrait être en mesure de proposer une suggestion pour ce qui suit.

Voici les données:

parent_id 1 -> children 3, 4, 5, 6, 7, 8
parent_id 2 -> children 3

child_id 3 -> parents 1, 2
child_id 4 -> parents 1
child_id 7 -> parents 6, 5, 4, 1

postgresql hierarchical-data recursive-query

2 commentaires

Vous pouvez commencer par n'importe quel sujet. Je suppose que je ne comprends pas entièrement votre question.

En effet. Donc si je commençais avec le sujet 1, quelle requête SQL me donnerait tous les enfants, et si avec le sujet 7, quelle requête donnerait à tous les parents? Etc.

3 Réponses :

10
votes

Pour obtenir tous les enfants du sujet 1, vous pouvez utiliser

WITH RECURSIVE c AS (
   SELECT 1 AS id
   UNION ALL
   SELECT sa.child_id
   FROM subject_associations AS sa
      JOIN c ON c.id = sa. parent_id
)
SELECT id FROM c;

2 commentaires

Cela semble fonctionner très bien - également en sens inverse. Un peu plus simple que je ne le pensais (j'ai continué à essayer de me joindre sur des sujets).

Vous vous joindriez avec sujets dans la requête externe.

5
votes

CREATE OR REPLACE FUNCTION func_finddescendants(start_id integer)
RETURNS SETOF subject_associations
AS $$
DECLARE
BEGIN
    RETURN QUERY
    WITH RECURSIVE t
    AS
    (
        SELECT * 
          FROM subject_associations sa
         WHERE sa.id = start_id
         UNION ALL
        SELECT next.*
          FROM t prev
          JOIN subject_associations next ON (next.parentid = prev.id)
    )
    SELECT * FROM t;
END;
$$  LANGUAGE PLPGSQL;

3 commentaires

Merci pour cette réponse. J'ai trouvé l'autre un peu plus facile à comprendre - je ne suis pas tout à fait sûr du fonctionnement de next / prev.

Vous êtes les bienvenus. prev est ce qui est actuellement dans votre table récursive, t dans ce cas. next est ce que vous allez ajouter à la table récursive après une jointure réussie. C'est très similaire à la réponse de Laurenz, je viens de donner à la table récursive un alias dans ma deuxième instruction select. Dans ma réponse, t est exactement comme c dans la réponse de Laurenz. Il fait simplement la jointure dans l'ordre inverse. J'ai sélectionné dans la table récursive puis rejoint la table subject_associations.

Merci encore. Pour le moment, je pense que je préfère utiliser l'autre réponse, qui semble bien fonctionner dans mon application, alors j'espère que cela ne vous dérange pas que je marque cela comme accepté.

3
votes

Essayez ceci

--- Table

-- DROP SEQUENCE public.data_id_seq;

CREATE SEQUENCE "data_id_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.data_id_seq
  OWNER TO postgres;



CREATE TABLE public.data
(
  id integer NOT NULL DEFAULT nextval('data_id_seq'::regclass),
  name character varying(50) NOT NULL,
  label character varying(50) NOT NULL,
  parent_id integer NOT NULL,
  CONSTRAINT data_pkey PRIMARY KEY (id),
  CONSTRAINT data_name_parent_id_unique UNIQUE (name, parent_id)
)
WITH (
  OIDS=FALSE
);

INSERT INTO public.data(id, name, label, parent_id) VALUES (1,'animal','Animal',0);
INSERT INTO public.data(id, name, label, parent_id) VALUES (5,'birds','Birds',1);   
INSERT INTO public.data(id, name, label, parent_id) VALUES (6,'fish','Fish',1); 
INSERT INTO public.data(id, name, label, parent_id) VALUES (7,'parrot','Parrot',5); 
INSERT INTO public.data(id, name, label, parent_id) VALUES (8,'barb','Barb',6);

--- Function 

CREATE OR REPLACE FUNCTION public.get_all_children_of_parent(use_parent integer) RETURNS integer[] AS
        $BODY$
        DECLARE
            process_parents INT4[] := ARRAY[ use_parent ];
            children INT4[] := '{}';
            new_children INT4[];
        BEGIN
            WHILE ( array_upper( process_parents, 1 ) IS NOT NULL ) LOOP
                new_children := ARRAY( SELECT id FROM data WHERE parent_id = ANY( process_parents ) AND id <> ALL( children ) );
                children := children || new_children;
                process_parents := new_children;
            END LOOP;
            RETURN children;
        END;
        $BODY$
LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION public.get_all_children_of_parent(integer) OWNER TO postgres


--- Test 

SELECT *  FROM data WHERE id = any(get_all_children_of_parent(1))
SELECT *  FROM data WHERE id = any(get_all_children_of_parent(5))
SELECT *  FROM data WHERE id = any(get_all_children_of_parent(6))

0 commentaires