0

I have a problem that looks relatively simple.

An activity entity has multiple invitation entities, the reference is from the invitation.activity_id => activity.

Invitation has a completion_date (when the user completes the activity) which can be zero (not complete) or a timestamp of when it was completed.

(I created a reverse relationship so I can go from the activity to the invitations, that works fine.)

I want to create a table view where each row is an activity, and it has two columns - total invitations and completed invitations.

The total is easy: switch on aggregation and count the number of invitations.

Calculating the number of completed is another matter.

I've created my own views field plugin (extended from boolean) which inserts an expression field instead of using the actual field.

class InvitationCompletionState extends Boolean {
  /**
   * Called to add the field to a query.
   */
  public function query() {
    $table_alias = $this->ensureMyTable();
    $this->query->addField(
      NULL,
      "IF($table_alias.completion_date > 0, 1, 0)",
      'completion_state'
    );
  }

But I can't aggregate it. I know aggregation doesn't work with everything, and I suppose this is a situation where it doesn't.

I wanted to use the boolean option (although INT might be better) because I can count the number of completed by simply SUMming the results.

This is the Views query if I try to use the aggregation

SELECT activity.name AS activity_name, activity.type AS activity_type, activity.start_date AS activity_start_date, activity.end_date AS activity_end_date, activity.estimated_time AS activity_estimated_time, COUNT(invitation_activity.id) AS invitation_activity_id, SUM(invitation_activity.completion_date) AS invitation_activity_completion_date, MIN(activity.id) AS id, MIN(invitation_activity.id) AS invitation_activity_id_1
FROM {activity} activity
LEFT JOIN {invitation} invitation_activity ON activity.id = invitation_activity.activity
GROUP BY activity_name, activity_type, activity_start_date, activity_end_date, activity_estimated_time
ORDER BY activity_start_date DESC

And it just forgets about inserting the expression.

Further investigation:

Pasting the following code into Workbench works and gives me the answer I want:

SELECT 
    activity.name AS activity_name, 
    activity.type AS activity_type, 
    activity.start_date AS activity_start_date, 
    activity.end_date AS activity_end_date, 
    activity.estimated_time AS activity_estimated_time, 
    COUNT(invitation_activity.id) AS invitation_activity_id, 
    SUM(IF(invitation_activity.completion_date > 0, 1, 0)) AS completion_state, 
    MIN(activity.id) AS id, 
    MIN(invitation_activity.id) AS invitation_activity_id_1

FROM activity activity

LEFT JOIN invitation invitation_activity ON activity.id = invitation_activity.activity

GROUP BY activity_name, activity_type, activity_start_date, activity_end_date, activity_estimated_time

ORDER BY activity_start_date DESC

which means that MySQL can handle the code, it's just that Views does not know how to generate it.

I need to be able to make this work in a standalone handler (for any future devs), writing a hook to modify a query is not really an option.

Any thoughts?

2

Yeah, no way to do it without major surgery to the Views Query class - the assumptions it makes are hardwired.

The only practical solution seems to be the Views Aggregator module, which has a D8 dev version. This provides a Views Table Style which performs aggregation functionality on the fetched records - in other words, you do an ordinary non-aggregated fetch and the module does the aggregating just before display.

It has the advantage that you can do all sorts of interesting things - far more than you can in SQL, against the fact that it adds to processing time for large data sets. (And I can think of other potential issues too.)

However it solves my problem, so that's good.

https://www.drupal.org/project/views_aggregator

The conversion from D7 to D8 is minimal (still uses hooks instead of plugins) but, as I say, it works.

  • In the end even Views Aggregator couldn't do some of the things I needed. So I switched to a custom-built table and added the Tabulator jquery plugin to provide clever table functionality. Not a great solution but it works well enough. – Adaddinsane Jan 14 at 9:53

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.