通过postgresql递归列出所有元素

Question

In my database I have basically a table (entity for Doctrine) called person and it looks like this:

class Person
{
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Person")
     */
    private $parent;
}

I'd like to build a méthod getAllChildren which returns all persons under person1 and recursively all persons under persons who have children.

I'd like to know if I can do this through one recursive SQL request.

UPDATE1

postgres version: 9.1

  -- Table: person

  -- DROP TABLE person;

  CREATE TABLE person
  (
      id serial NOT NULL,
      parent_id integer,
      nom character varying(255) DEFAULT NULL::character varying,
      prenom character varying(255) DEFAULT NULL::character varying,
      age integer,
      description text,
      statut character varying(255) DEFAULT NULL::character varying,
      CONSTRAINT person_pkey PRIMARY KEY (id),
      CONSTRAINT fk_7cbkzkd63HRzVK8e FOREIGN KEY (parent_id)
      REFERENCES person(id) MATCH SIMPLE
  )

UPDATE2

I've found http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/, this is pretty much what I need except my model is limitless

Answer 1

In PostgreSQL you can use a recursive CTE to graciously deal with recursion at table level. For your specific question, the solution would be this (add the other columns from your table as needed; omitted here for brevity and focus):

WITH RECURSIVE p(parent, child, refs) AS (
  SELECT parent_id, id, 1 FROM person WHERE parent_id IS NOT NULL
UNION
  SELECT parent_id, child, refs+1 
  FROM person JOIN p ON id = parent)
SELECT * FROM p WHERE parent IS NOT NULL 
ORDER BY parent, child;

The trick here is that you have to work your way up the hierarchy in order to find deeper levels of relationship; the refs column indicates the separation between parent and child. (Should you go down the hierarchy from any given person.id you can not link back up more than 1 level due to the way that the recursive CTE works, AFAIK. You probably can, but probably not as elegantly as in the above solution.)

I have found that it is oftentimes useful to include self-references, i.e. records where parent = child and refs = 0. The solution then becomes:

  WITH RECURSIVE p(parent, child, refs) AS (
    SELECT id, id, 0 FROM person WHERE parent_id IS NOT NULL   -- the self-reference
  UNION
    SELECT parent_id, child, refs+1 
    FROM person JOIN p ON id = parent)
  SELECT * FROM p WHERE parent IS NOT NULL 
UNION
  SELECT id AS parent, id AS child, 0 AS refs   -- add the top-level node
  FROM person
  WHERE parent_id IS NULL
ORDER BY parent, child;

For ease of use, you can wrap the query in a view and then you simply create an entity of the view in Doctrine to get at the data.