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.