projection pomm many to many

Question

I use pomm in a symfony 3.4 project. Here is my use case.

In a model generated from a Pomm command, I have this method implemented:

    $sql = <<<SQL
        select tableA.*, array_agg(tableB) as tableB
        from tableA, tableA_tableB, tableA
        where tableA.id=$id and tableA.id=tableA_tableB.tableA_id and tableB.id=tableA_tableB.tableB_id
        group by tableA.id

SQL;

    $projection = $this->createProjection()
        ->setField('tableB', 'tableB', TableB::class)
    ;

    return $this->query($sql, [], $projection);

I have a tableA and tableB. A tableA can have 0 to N tableB and a tableB can have 0 to N tableA. My problem is that I can not get a collection of tableB in the field 'tableB' of my projection. Table :: class is a flexible entity. What should I do ? Thank you !

Answer 1

1. You should never use the * in your queries unless you are in an interactive terminal. This is the reason the projection class exists.
2. You are strongly advised to use the query parameters $* to ensure they are escaped in order to protect your application against SQL injection.

```

$sql = <<<SQL
SELECT
  :projection
FROM
  table_a
  INNER JOIN table_a_b USING (table_a_id)
  INNER JOIN table_b USING (table_b_id)
WHERE
  table_a_id = $*
GROUP BY table_a_id
SQL;

$projection = $this->createProjection()
    ->setField('table_bs', 'array_agg(table_b)', 'schema.table_b[]')
    ;
$sql = strtr($sql, [':projection' => (string) $projection]);

return $this->query($sql, [$table_a_id], $projection);

The Projection setField() method adds to the default table_a projection a new field named table_bs of type array of table_b. This query will dump an iterator of TableA entities each with their corresponding array of TableB entities under the key table_bs.