If I have two tables, say foos and bars, in a many-to-many relationship (via a bars_foos join table, in this case), what is the best way in CakePHP 3.0 to include an array of the IDs of Bars associated with each Foo in the JSON returned by the RequestHandler through the _serialize property?
Specifically, I would like to be able to visit site/foos.json and see something like this:
{
"foos": [
{
"id": 1,
"name": "A foo",
...
"bar_ids": [1, 3, 5]
},
{
"id": 2,
"name": "Foo too",
...
"bar_ids": [2]
]
}
As a bonus, this should still work if each Foo also belongsToMany Bazzes, so that I end up getting, for example,
...
"bar_ids": [1, 3, 5],
"baz_ids": [37, 42]
...
Is there a straightforward way to achieve this that's easy to apply to many models and doesn't result in an excessive number of database queries?
What I've tried so far:
I have managed to roughly accomplish this in two ways, but neither seems ripe for quickly and easily generalizing to many models. I'm wary of the efficiency of adding new queries or query fields to get information that is likely already sitting somewhere in CakePHP, if I could only find the right syntax to bring it out.
Virtual field
One method is to create a a virtual field though a _getBarIds() function and setting protected $_virtual = ['bar_ids'] in the Foo Entity. The function uses the TableRegistry to find and query the bars_foos join table for the Bar entities associated to the current Foo, and return them as a PHP array. The key functionality is roughly like 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, but manually adding these _get<Model>Ids() functions for every association in my database isn't appealing, and performing one or more new database hits for every row I want to retrieve is certainly not ideal.
Aggregation in the controller query
Another method is to add joins to the index query in the Foos Controller to aggregate over the join tables. The key component here looks like 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 from 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 a coalesce(..., '') around the string_agg(...) to return "[]" instead of NULL when there aren't any associated Bars, and throw a distinct at the start of the first argument to string_agg if multiple joins are returning duplicates in the bar_id column, but this is the basic idea.
This approach is somewhat more appealing to me because it gets everything in a single query to the database, but it also feels a little too manual, and has the added disadvantage of returning the array as a string so that in the JSON it appears as "bar_ids": "[1, 3, 5]", with quotes around what should be an array instead of a string (in my current implementation with PostgreSQL's string_agg, anyway).
This doesn't seem like a particularly crazy feature---have I just missed something obvious that gives a much simpler way to accomplish the task in general?
