Yesterday I looked into adding a key-value pair such as "tag_ids": [1, 2, 3] to a JSON object returned by a serialized CakePHP view, for Bookmarks belonging to many Tags. My solution produces the desired result, but involves the execution of a new database query for every bookmark, on top of the one that retrieved the record in the first place. This is not exactly desirable behaviour, so today I looked into other options. I still don’t have what I consider to be an optimal solution, but did come up with an alternative that manages to pull all the associated Tag ids in the same query that retrieves the current batch of Bookmarks.
There is a new disadvantage introduced by this solution, because the resulting KVP contains a string instead of an array, looking like "tag_ids": "[1, 2, 3]" . Additionally, the code is nowhere near ready to be generalized for easy addition to multiple controllers, nor even particularly elegant in terms of making use of Cake’s routines for inflection etc. Nevertheless, I want to record it while it’s still fresh in my mind.
The idea is to use the CakePHP query builder to LEFT JOIN the Bookmarks model’s table to the join table that contains its many-to-many relationships with the Tags model. The query is then grouped by all of the Bookmarks fields, and the tag_id field from the join table is aggregated into a single comma-delimited string. I’m using PostgreSQL, so accomplish this with the string_agg command. Here’s a working example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
// In src/Controller/Bookmarks.php public function index () { // Array of Bookmarks fields to include $bookmark_fields = ['id', 'title' /* add other fields here */ ]; // PostgreSQL function that will concatenate over the GROUP $concat = "string_agg(BookmarksTags.tag_id::text, ',')"; // Aggregated field from the join table $agg_fields = [ 'tag_ids' => "'[' || $concat || ']'" ]; // For the group-by, we need Bookmarks.id, Bookmarks.title, etc. $group_by_fields = array_map( function ($f) { return 'Bookmarks.' . $f; }, $bookmark_fields ); $bookmarks = $this->Bookmarks->find('all') ->select(array_merge($group_by_fields, $agg_fields)) ->autofields(false) ->join([ 'table' => 'bookmarks_tags', 'alias' => 'BookmarksTags', 'type' => 'LEFT', 'conditions' => 'BookmarksTags.bookmark_id = Bookmarks.id' ]) ->group($group_by_fields); // Store the query and set it to be serializable $this->set('bookmarks', $bookmarks); $this->set('_serialize', ['bookmarks']); } |
I found that I had to set autofields(false) to avoid having CakePHP automatically include every field in the join table, and therefore needing to add them to the group-by clause or aggregating them in some fashion.
There are a few improvements that can be made right away. Additional joins can be added, but will introduce multiples in the concatenated string unless the string aggregation is made distinct, string_agg(distinct Book...). Bookmarks with no tags yield "tag_ids": null, but can be made to give "tag_ids": "[]" by wrapping the aggregation in coalesce(..., '').
Larger-scale improvements could involve outputting an array instead of a string in the JSON, perhaps by returning a postgres array instead of a CSV string, and teaching Cake how to deal with that properly. Beyond that, not hard-coding the table names, generating the list of joins automatically, and generally wrapping this all up into a behaviour or trait would be nice steps to take.