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...)

  • 1
    No there’s no reasonable sql query you could write that will parse a php serialised string. If you want to query it, the data needs to be in a structure that the DBMS understands natively – Clive Nov 24 '18 at 13:44
  • @clive Thanks. I probably need to rewrite the question, but do you think I could add a lookup table? e.g. new content type with fields for node ID, user ID and counter, one row per user, and in my original table, add an entity reference (unlimited values), and add a relationship to the view...? – William Turrell Nov 24 '18 at 16:25
  • 1
    Yeah I don’t see any reason why not - you’ll need to make the table known to Views via hook_views_data, and describe the relationship to the node entity type via hook_views_data_alter, but yes I’d say you’re on the right track with that approach – Clive Nov 24 '18 at 17:07
  • I guess I'd try so solve this with a view (I'm speaking of SQL views, not the Drupal module views) – Hudri Nov 25 '18 at 9:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.