Tree structure with hydrated model in Pomm-project

Question

I have simple database table which implements tree structure via parent_id attribute. Something like this:

+----+------+-----------+
| id | name | parent_id |
+----+------+-----------+
|  1 | test | null      |
+----+------+-----------+
|  2 | tes2 | 1         |
+----+------+-----------+
|  3 | tes3 | 2         |
+----+------+-----------+
|  4 | tst  | 2         |
+----+------+-----------+

I want to obtain PHP object with tree structure. So object Category will have property subcategories, which will be list of Category objects and so on. I want to obtain this object directly from PostgreSQL database via recursive sql query with Pomm. The goal is not to traverse obtained data and build such object in PHP. I want straight process PostreSQL -> Pomm -> Object.

As for now, I get what I want by only in first level. So first level Category has subcategories, which is list of Category entities. However next level (depth 2) has no subcatogories.

So far I have this:

$sql = <<<SQL
with recursive
cat as (select c.* FROM :category c where parent_id is null),
subcat as (
select c.* from :category c join cat on c.parent_id=cat.id
union all
select c.* from :category c join subcat on c.parent_id=subcat.id
)
select :projection from cat cc, subcat
where cc.id=subcat.parent_id
group by :group_fields
SQL;

$projection = $this->createProjection()
    ->setField('subcategories', 'array_agg(subcat)', 'public.category[]');
$sql = strtr($sql, [
        ':category' => $this->structure->getRelation(),
        ':projection' => $projection->formatFieldsWithFieldAlias('cc'),
        ':group_fields' => $this->createProjection()->formatFields('cc'),
]);

My question if this is possible with Pomm and if yes, how?

score 1 · Answer 1 · answered Jul 21 '15 at 20:46

What you want to achieve is not really possible directly because in Pomm, for performances reasons, when a query is performed, an iterator wraps the database cursor on results.

$iterator = $this->query($sql, $array_of_parameters);

foreach ($iterator as $entity) {
   $entity->getParentId();
}

Every time a data is fetched from the iterator, the converter system turns it into an entity. But entities do not know about database hence they cannot fetch more data using their accessors.

A trivial idea would be to fetch a single result containing all the results as nested entities:

with recursive
cat as (
select * from test_tree tt where not exists (select parent_id from test_tree tt2 where tt2.parent_id = tt.id)
union all
select tt.*, array_agg(child) from test_tree tt join cat child on tt.id = child.parent_id group by tt.id
)
select * from cat

But unfortunately, it is not possible to use aggregate functions in the recursive term of a CTE.

Another idea would be to index results on id giving children for every parent_id and using that Pomm iterators are scrollable to fetch them:

with
  tree as (
    select
      tt.id,
      array_agg(child) as children
    from
      test_tree tt
      join lateral (select * from test_tree tt2 where tt2.parent_id = tt.id) child on (true) group by tt.id
  )
select
  idx as id,
  tree.children
from
  generate_series(1, (select max(id) from test_tree)) idx
  left join tree on tree.id = idx

which outputs:

┌────┬─────────────────────────────────────────┐
│ id │                children                 │
├────┼─────────────────────────────────────────┤
│  1 │ {"(2,\"test 2\",1)","(3,\"test 3\",1)"} │
│  2 │ {"(4,\"test 4\",2)","(5,\"test 5\",2)"} │
│  3 │ {"(6,\"test 6\",3)"}                    │
│  4 │ ¤                                       │
│  5 │ ¤                                       │
│  6 │ {"(7,\"test 7\",6)"}                    │
│  7 │ ¤                                       │
└────┴─────────────────────────────────────────┘
(7 rows)

Then the result set would be ordered by parent_id so $iterator->get($parent_id) would return an array of children entities (or null) but this looks more like a hack than a real feature.

Taking the problem by the other end, it seems possible to create dedicated flexible entities that embed a nested set design pattern internally to recurse on children.

Tree structure with hydrated model in Pomm-project

1 Answers1