0

I have an "Activity" entity and an "Invitation" entity which links to the activity by its ID (also to the invited user but that's not important here).

I want a relationship in a view where various calculations are done on the invitations to an activity.

I found this helpful question Reverse Entity Relationship for Views which gave me the core of what I needed.

Using that as a model I added this to the Invitation ViewsDataHandler:

public function getViewsData() {
  $data = parent::getViewsData();

  $data['activity']['invitation'] = [
    'title' => t('Activity invitations'),
    'help' => t('All the invitations for an activity.'),
    'relationship' => [
      'id' => 'entity_reverse',
      'label' => 'Activity invitations',

      #'base' => 'invitation',
      #'base field' => 'id',

      'field table' => 'invitation',
      'field field' => 'activity',
    ],
  ];

  return $data;
}

I omitted the base table data because the field table is the same - an invitation only links to one activity. Not that it makes any difference.

So when I create a relationship in views I get this MySQL...

SELECT activity.id AS id FROM {activity} activity
  LEFT JOIN {invitation} invitation ON activity.id = invitation.activity
  LEFT JOIN {} _activity ON invitation.entity_id = _activity. 
  LIMIT 11 OFFSET 0;

And I just can't figure out why.

For completeness, here's the SELECT when I include the base table:

SELECT activity.id AS id, _activity.id AS _activity_id FROM {activity} activity
  LEFT JOIN {invitation} invitation ON activity.id = invitation.activity
  LEFT JOIN {invitation} _activity ON invitation.entity_id = _activity.id
  LIMIT 11 OFFSET 0;

I've tried other variations but I always get two LEFT JOINs, and always the extra _activity.

Just so there's no confusion, the activity field in the invitation table is called "activity", it's not called "activity_id".

  • Does the SQL query contain LEFT JOIN {invitation} or LEFT JOIN {}? – kiamlaluno Oct 22 '18 at 9:38
  • Those responses are exactly what views generated. Two LEFT JOINs. – Adaddinsane Oct 22 '18 at 10:12
  • Is it possibly because the field name is the same as the entity name? (I'm still at the dev stage so it would be possible to change that, though irritating). – Adaddinsane Oct 22 '18 at 10:43
  • In the second SQL query, you say to include the base table. I take that for the first one you don't include it. Is that exact? – kiamlaluno Oct 22 '18 at 13:07
  • That is correct. – Adaddinsane Oct 22 '18 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.