hook_views_data join table on multiple columns

Question

I have 3 tables lms_user, lms_answer_initial and lms_model_answer and need to join them to display a view on certain columns.

I can successfully join the first 2 tables (lms_user, lms_answer_initial ) by using 'relationship'

$data['lms_answer_initial']['uid'] = array(
    'title' => t('Drupal user ID'),
    'help' => t('The Drupal user ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'relationship' => array(
      'title' => t('User(LMS)'),
      'help' => t('Relate content to the LMS user.'),
      'base' => 'lms_user',
      'base field' => 'uid',
      'label' => t('user'),
      'handler' => 'views_handler_relationship',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_equality',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument',
    ),
  );

but I has no idea how to join the third table on 3 columns.

Here is my complete code in hook_views_data:

$data['lms_answer_initial']['table']['group'] = t('LMS Answer Initial');
$data['lms_answer_initial']['table']['base'] = array(
    'title' => t('Answers (Report)'),
    'help' => t('Answers for the Learning Management System.'),
    'weight' => -10,
  );
$data['lms_answer_initial']['table']['join'] = array(
    'lms_model_answer' => array(
        'left_table' => 'lms_model_answer',
        'left_field' => 'fid',
        'table' => 'lms_answer_initial',
        'field' => 'fid',
    ),
);

  $data['lms_answer_initial']['uid'] = array(
    'title' => t('Drupal user ID'),
    'help' => t('The Drupal user ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'relationship' => array(
      'title' => t('User(LMS)'),
      'help' => t('Relate content to the LMS user.'),
      'base' => 'lms_user',
      'base field' => 'uid',
      'label' => t('user'),
      'handler' => 'views_handler_relationship',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_equality',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument',
    ),
  );
  $data['lms_answer_initial']['fid'] = array(
    'title' => t('File ID'),
    'help' => t('EPUB File ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'relationship' => array(
      'title' => t('LMS Model Answer'),
      'help' => t('Relate activity model answer.'),
      'handler' => 'views_handler_relationship',
      'base' => 'lms_model_answer',
      'base field' => 'fid',
      'label' => t('model answer'),
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_equality',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument',
    ),
  );
  $data['lms_answer_initial']['book_id'] = array(
    'title' => t('Book ID'),
    'help' => t('The book ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['lms_answer_initial']['unit_id'] = array(
    'title' => t('Unit ID'),
    'help' => t('The unit ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['lms_answer_initial']['activity_id'] = array(
    'title' => t('Activity ID'),
    'help' => t('The activity ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'relationship' => array(
      'title' => t('LMS Model Answer'),
      'help' => t('Relate activity model answer.'),
      'handler' => 'views_handler_relationship',
      'base' => 'lms_model_answer',
      'base field' => 'activity_id',
      'label' => t('model answer'),
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['lms_answer_initial']['question_id'] = array(
    'title' => t('Question ID'),
    'help' => t('The question ID.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'relationship' => array(
      'title' => t('LMS Model Answer'),
      'help' => t('Relate activity model answer.'),
      'handler' => 'views_handler_relationship',
      'base' => 'lms_model_answer',
      'base field' => 'question_id',
      'label' => t('model answer'),
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['lms_answer_initial']['answer'] = array(
    'title' => t('Answer'),
    'help' => t('The answer.'),
    'field' => array(
      'handler' => 'views_handler_field',
    ),
  );
  $data['lms_answer_initial']['is_correct'] = array(
    'title' => t('Correct'),
    'help' => t('Indicates whether the answer is correct.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
  );
  $data['lms_answer_initial']['changed'] = array(
    'title' => t('Change date'),
    'help' => t('The date when the answer was changed.'),
    'field' => array(
      'handler' => 'views_handler_field_date',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort_date',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_date',
    ),
  );

And here is the resulting query

SELECT lms_answer_initial.uid AS lms_answer_initial_uid, >lms_answer_initial.book_id AS lms_answer_initial_book_id, lms_answer_initial.unit_id AS lms_answer_initial_unit_id, lms_answer_initial.activity_id AS lms_answer_initial_activity_id, lms_answer_initial.question_id AS lms_answer_initial_question_id, lms_answer_initial.answer AS lms_answer_initial_answer, lms_answer_initial.is_correct AS lms_answer_initial_is_correct, lms_answer_initial.fid AS lms_answer_initial_fid

FROM {lms_answer_initial} lms_answer_initial

LEFT JOIN {lms_user} lms_user_lms_answer_initial ON lms_answer_initial.uid = lms_user_lms_answer_initial.uid

LEFT JOIN {lms_model_answer} lms_model_answer_lms_answer_initial ON lms_answer_initial.fid = lms_model_answer_lms_answer_initial.fid

LIMIT 30 OFFSET 0

How to add the following condition to the query?

lms_answer_initial.activity_id = lms_model_answer_lms_answer_initial.activity_id and lms_answer_initial.question_id = lms_model_answer_lms_answer_initial.question_id

Appreciate any help on this.

Answer 1

In Drupal 8 I used the following code:

  $data['table_a']['table_a_some_field'] => [
    'field' => [
      'id' => 'standard',
    ],
    'filter' => [
      'id' => 'numeric',
    ],
    'relationship' => [
      'id' => 'standard',
      'label' => t('Table B'),
      'base' => 'table_b',
      'base field' => 'table_b_some_field',
      'extra' => [
        [
          'left_field' => 'table_a_field_2',
          'field' => 'table_b_field_2',
        ],
      ],
    ],
  ],

Of course, table_b should be defined separately with its fields as well.

Answer 2

I finally get this done by following the solution here: http://eworldproblems.mbaynton.com/2014/04/drupal-views-join-on-multiple-columns/

Here is what I have actually do. In hook_views_data, add join_handler:

'relationship' => array(
          'title' => t('Model answer relationship on fid'), //Title shown when adding the relationship
          'base' => 'lms_model_answer', //name of table to join with
          'base field' => 'fid',        //name of the field on the joined table
          'handler' => 'views_handler_relationship',
          'join_handler' => 'lms_reports_join_lms_answer_initial_lms_model_answer',
          'label' => t('model answer fid'),
          'type' => 'LEFT',       
        ),

Then implement my own customized handler, in *.views.inc:

class lms_reports_join_lms_answer_initial_lms_model_answer extends views_join
{
  public function build_join($select_query, $table, $view_query) {
    $select_query->addJoin('INNER', $this->table, $table['alias'], "lms_answer_initial.fid = ${table['alias']}.fid AND lms_answer_initial.activity_id = ${table['alias']}.activity_id AND lms_answer_initial.question_id = ${table['alias']}.question_id");
  }
}

Hope this help anyone else who encounter the same problem.

Answer 3

You can use hook_views_query_alter(&$view, &$query).

And in this hook you can write:

$query->add_where_expression(0,'lms_answer_initial.activity_id = lms_model_answer_lms_answer_initial.activity_id');