/**
* Returns an SQL fragment needed to connect a context entity table to its
* CM custom fields data in the CM system
*
* @param string $contextlevel Shortname of the context level we are looking for fields
* related to
* @param array $fieldids List of ids of field records we are displaying
* @param string $instancefield Database field representing the context id
* @return string The appropriate SQL fragment
*/
function get_custom_field_sql($fieldids, $instancefields)
{
global $CURMAN;
$contexts = $CURMAN->db->get_records_list('crlm_field_contextlevel', 'fieldid', implode(',', $fieldids), 'contextlevel');
$levels = context_level_base::get_all_context_levels();
$contextlevel = '';
$fragment = array();
if (!empty($contexts)) {
//add a join for each profile field
foreach ($contexts as $context) {
if ($contextlevel != $context->contextlevel) {
$contextlevel = $context->contextlevel;
$contextname = $levels[$contextlevel]->get_component_string('', '');
$instancefield = $instancefields[$contextname];
//have one ot more profile field we're joining, so join the context table at the top level
$context_level = context_level_base::get_custom_context_level($contextlevel, 'block_curr_admin');
$fragment[] .= " LEFT JOIN {$CURMAN->db->prefix_table('context')} {$contextname}context\n ON {$instancefield} = {$contextname}context.instanceid\n AND {$contextname}context.contextlevel = {$contextlevel}";
}
$field = new field($context->fieldid);
$identifier = "customfielddata_{$context->fieldid}";
$fragment[] .= " LEFT JOIN {$CURMAN->db->prefix_table($field->data_table())} {$identifier}" . " ON {$contextname}context.id = {$identifier}.contextid" . " AND {$identifier}.fieldid = {$context->fieldid}";
}
}
return implode("\n", $fragment);
}
