Moodle自定义下拉字段筛选课程报错：未知列cd.shortname

我在Moodle中创建了课程自定义字段，希望实现基于用户偏好的课程筛选功能：例如语言筛选字段包含ENG、SP、IT选项，当用户选择ENG时，仅显示语言自定义字段为ENG的课程。

require_once(__DIR__ . '/../config.php');

$PAGE->set_context(context_system::instance());
$PAGE->set_pagelayout('standard');
$PAGE->set_title("Custom Fields");
$PAGE->set_heading("All Custom Fields for Courses");

echo $OUTPUT->header();

$ftopics = $DB->get_records('customfield_field', array('shortname' => 'topic'));
$flevels = $DB->get_records('customfield_field', array('shortname' => 'level'));
$flanguages = $DB->get_records('customfield_field', array('shortname' => 'language'));

echo '<form method="get" action="#">'; // Start a form

// ----- TOPIC OPTIONS -----
echo '<select name="topic_filter" id="topic_filter">'; // Start a dropdown/select element
echo '<option value="">All Topics</option>'; // Include an option for all topics

foreach ($ftopics as $topic) {
    $configdata = json_decode($topic->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

// ----- LEVEL OPTIONS -----
echo '<select name="level_filter" id="level_filter">';
echo '<option value="">All Levels</option>'; // Include an option for all levels

foreach ($flevels as $level) {
    $configdata = json_decode($level->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

// ----- LANGUAGE OPTIONS -----
echo '<select name="language_filter" id="language_filter">';
echo '<option value="">All Languages</option>'; // Include an option for all languages

foreach ($flanguages as $language) {
    $configdata = json_decode($language->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

echo '<input type="submit" value="Filter">';
echo '</form>'; // End the form

// Fetch filter values
$topicFilter = optional_param('topic_filter', null, PARAM_TEXT);
$levelFilter = optional_param('level_filter', null, PARAM_TEXT);
$languageFilter = optional_param('language_filter', null, PARAM_TEXT);

// Construct SQL query based on the selected filters
$sql = "SELECT c.id, c.fullname, c.summary
        FROM {course} c";

// Add WHERE clause based on selected filters
$whereClause = array();
if (!empty($topicFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'topic' AND cd.value = :topic_filter AND cd.instanceid = c.id)";
}
if (!empty($levelFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'level' AND cd.value = :level_filter AND cd.instanceid = c.id)";
}
if (!empty($languageFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'language' AND cd.value = :language_filter AND cd.instanceid = c.id)";
}

// Combine WHERE clauses with AND
if (!empty($whereClause)) {
    $sql .= ' WHERE ' . implode(' AND ', $whereClause);
}

// Execute the query
$params = array(
    'topic_filter' => $topicFilter,
    'level_filter' => $levelFilter,
    'language_filter' => $languageFilter
);

$courses = $DB->get_records_sql($sql, $params);

// Display the titles of the filtered courses
foreach ($courses as $course) {
    echo '<div>';
    echo '<h2>' . html_writer::link(new moodle_url('/course/view.php', array('id' => $course->id)), $course->fullname) . '</h2>';
    echo '<p>' . $course->summary . '</p>';
    echo '</div>';
}

echo $OUTPUT->footer();
?>

Error reading from database
More information about this error
Debug info: Unknown column 'cd.shortname' in 'where clause'

SELECT c.id, c.fullname, c.summary
FROM mdl_course c WHERE c.id IN (SELECT cd.instanceid FROM mdl_customfield_data cd WHERE cd.shortname = 'level' AND cd.value = ? AND cd.instanceid = c.id)
[array (
0 => 'HEI',
)]

Error code: dmlreadexception

我意识到之前存在误用选项文本（如ENG）而非选项ID进行筛选的问题，现寻求该报错的解决方法及其他优化建议。

内容的提问来源于stack exchange，提问作者thebaby