TL;DR: This is less a question about how to write a Views sort plugin, and more whether I can serialise key/value pairs and selectively sort by one of them using SQL functions.
I have a content type with a comments field.
There's a corresponding view with an exposed filter where you can select a user from a dropdown.
I wrote a field handler which scans the comments (remember: a comment field only contains settings and stats, comments are their own entities, so you have to load them separately) to see if the selected user has left a comment on each node.
This generates a 'Commented?' column for the view: Yes / No, no. of comments etc.
Currently, the result isn't stored in the DB, it runs every time.
The challenge is making the column sortable, given the results are different for each user you may select.
I'm assuming the only practical way to do it would be to store the result in the DB (to be updated after comments are created/deleted), so I can use $this->query->addOrderBy() in SortPluginBase.
It would need to be scaleable though - i.e. a single field with all the values, not one field per person.
If I serialised the value (e.g. uid/result key/value pairs) and stored that in a field, it would be easy enough to rewrite my field handler to display the correct result in an unsorted view.
But, for the sorting, is there any SQL I could use to extract the value of the correct key? (This would also need to cope with situations where a new user has been added, but no data is stored for them on the legacy comments, e.g. a default/fallback value if the key is not found).
Or would anyone recommend an entirely different approach? (There is HOOK_views_pre_render(), but looping through results there seems a bit ugly...)