has and belongs to many - Return JSON array of associated entity IDs in CakePHP 3.0 belongsToMany relationship -


if have 2 tables, foos , bars, in many-to-many relationship (via bars_foos join table, in case), best way in cakephp 3.0 include array of ids of bars associated each foo in json returned requesthandler through _serialize property?

specifically, able visit site/foos.json , see this:

{   "foos": [     {       "id": 1,       "name": "a foo",       ...       "bar_ids": [1, 3, 5]     },     {       "id": 2,       "name": "foo too",       ...       "bar_ids": [2]   ] }

as bonus, should still work if each foo belongstomany bazzes, end getting, example,

      ...       "bar_ids": [1, 3, 5],       "baz_ids": [37, 42]       ...

is there straightforward way achieve that's easy apply many models , doesn't result in excessive number of database queries?

what i've tried far:

i have managed accomplish in 2 ways, neither seems ripe , generalizing many models. i'm wary of efficiency of adding new queries or query fields information sitting somewhere in cakephp, if find right syntax bring out.

virtual field

one method create a virtual field though _getbarids() function , setting protected $_virtual = ['bar_ids'] in foo entity. function uses tableregistry find , query bars_foos join table bar entities associated current foo, , return them php array. key functionality this:

// in src/model/entity/foo.php, protected function _getbarids: $bars_foos = tableregistry::get('bookmarks_tags'); $these_bars = $bars_foos->find()     ->select(['bar_id'])     ->where(['foo_id' => $this->id]);

this works reasonably well, manually adding these _get<model>ids() functions every association in database isn't appealing, , performing 1 or more new database hits every row want retrieve not ideal.

aggregation in controller query

another method add joins index query in foos controller aggregate on join tables. key component here looks this:

// in src/controller/fooscontroller.php, public function index: $query = $this->foos->find('all')     ->select(['foos.id', 'foos.name',       'bar_ids' => "'[' || string_agg(barsfoos.bar_id::text, ',') || ']'"     ])     ->autofields(false) // don't include other fields bars_foos     ->join([       'table' => 'bars_foos',       'alias' => 'barsfoos',       'type' => 'left',       'conditions' => 'barsfoos.foo_id = foos.id'     ])     ->group(['foos.id', 'foos.name']); $this->set('foos', $query); $this->set('_serialize', ['foos']);

you can wrap coalesce(..., '') around string_agg(...) return "[]" instead of null when there aren't associated bars, , throw distinct @ start of first argument string_agg if multiple joins returning duplicates in bar_id column, basic idea.

this approach more appealing me because gets in single query database, feels little manual, , has added disadvantage of returning array string in json appears "bar_ids": "[1, 3, 5]", quotes around should array instead of string (in current implementation postgresql's string_agg, anyway).

this doesn't seem particularly crazy feature---have missed obvious gives simpler way accomplish task in general?

result formatters

i use containments , result formatters. containing association require single additional query per association retrieve associated data, can use create whatever additional properties like.

here's basic example, should pretty self-explantory, iterates on retrieved rows, , adds new property containing ids of associated records.

$query = $this->foos     ->find()     ->contain(['bars'])     ->formatresults(         function ($results) {             /* @var $results \cake\datasource\resultsetinterface|\cake\collection\collectioninterface */             return $results->map(function ($row) {                 /* @var $row array|\cake\datasource\entityinterface */                 $ids = [];                 foreach ($row['bars'] $barrow) {                     $ids[] = $barrow['id'];                 }                 $row['bar_ids'] = $ids;                 return $row;             });         }     );

reusable formatters , custom finders

to keep things dry can make table provide formatter, , wrap in custom finder.

public function formatwhateverresults($results) {     // ... }  public function findwhatever(query $query, array $options) {     $query         ->contain(['bars'])         ->formatresults([$this, 'formatwhateverresults']);     return $query; } 
$query = $this->foos->find('whatever');

further automatization

also can of course automate further, example inspecting tables associations , process ids of contained belongstomany ones, like

/**  * @param \cake\datasource\resultsetinterface|\cake\collection\collectioninterface $results  */ public function formatwhateverresults($results) {     $associations = $this->associations()->type('belongstomany');     return $results->map(function ($row) use ($associations) {         /* @var $row array|\cake\datasource\entityinterface */         foreach ($associations $assoc) {             /* @var $assoc \cake\orm\association */             $property = $assoc->property();             if (isset($row[$property])) {                 $ids = [];                 foreach ($row[$property] $assocrow) {                     $ids[] = $assocrow['id'];                 }                 $row[inflector::singularize($property) . '_ids'] = $ids;             }         }         return $row;     }); }

see also


-