0

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.

2

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.

0

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');

  • Thanks. Just tried but it add to the expression to overall sql instead in the Join condition. Resulting query now becomes: SELECT * 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 INNER JOIN {lms_model_answer} lms_model_answer_lms_answer_initial ON lms_answer_initial.fid = lms_model_answer_lms_answer_initial.fid WHERE (( (lms_answer_initial.activity_id = lms_model_answer_lms_answer_initial.activity_id) )) LIMIT 30 OFFSET 0 – Bowie May 3 '16 at 2:09
0

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.

Your Answer

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

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