如何通过SQL查询Moodle数据库中满足双自定义字段条件的课程记录

问题原因 #

你原来的SQL逻辑存在本质错误：每条mdl_customfield_data记录只对应一个自定义字段（要么是language，要么是institutions），不可能同时满足f.shortname = 'institutions'和f.shortname = 'language'两个条件，所以用AND会返回空结果；用OR则会把满足任意一个条件的课程全部列出，不符合「同时满足两个条件」的需求。

正确解法 #

方法1：多次关联自定义字段表 #

通过两次JOIN分别关联language和institutions的字段数据，直接筛选同时满足两个条件的课程：

SELECT c.*
FROM mdl_course c
-- 关联并筛选language=1的课程
JOIN mdl_customfield_data d_lang ON c.id = d_lang.instanceid
JOIN mdl_customfield_field f_lang ON f_lang.id = d_lang.fieldid 
    AND f_lang.shortname = 'language'
-- 关联并筛选institution包含2的课程
JOIN mdl_customfield_data d_inst ON c.id = d_inst.instanceid
JOIN mdl_customfield_field f_inst ON f_inst.id = d_inst.fieldid 
    AND f_inst.shortname = 'institutions'
WHERE d_lang.value = '1'
  AND (d_inst.value LIKE '%,2,%' 
       OR d_inst.value LIKE '2,%' 
       OR d_inst.value LIKE '%,2' 
       OR d_inst.value = '2')

方法2：子查询筛选课程ID #

先分别找出满足单个条件的课程ID，再取交集得到同时满足两个条件的课程：

SELECT c.*
FROM mdl_course c
WHERE c.id IN (
    -- 获取language=1的课程ID
    SELECT instanceid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid
    WHERE f.shortname = 'language' AND d.value = '1'
)
AND c.id IN (
    -- 获取institution包含2的课程ID
    SELECT instanceid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid
    WHERE f.shortname = 'institutions'
      AND (d.value LIKE '%,2,%' 
           OR d.value LIKE '2,%' 
           OR d.value LIKE '%,2' 
           OR d.value = '2')
)

方法3：GROUP BY + HAVING聚合判断 #

先筛选出满足任意一个条件的记录，再通过分组确保课程同时拥有两个符合条件的字段记录：

SELECT c.*
FROM mdl_course c
JOIN mdl_customfield_data d ON c.id = d.instanceid
JOIN mdl_customfield_field f ON f.id = d.fieldid
WHERE (f.shortname = 'language' AND d.value = '1')
   OR (f.shortname = 'institutions' 
       AND (d.value LIKE '%,2,%' 
            OR d.value LIKE '2,%' 
            OR d.value LIKE '%,2' 
            OR d.value = '2'))
GROUP BY c.id
HAVING COUNT(DISTINCT f.shortname) = 2

优化小技巧 #

如果你的数据库是MySQL，可以用FIND_IN_SET函数简化institution的筛选条件，替代多个LIKE判断：

-- 替代原来的LIKE条件
FIND_IN_SET('2', d_inst.value) > 0

注意：FIND_IN_SET仅适用于逗号分隔的字符串，数据量较大时可能影响性能，这是Moodle现有结构下的折中方案。

内容的提问来源于stack exchange，提问作者J. Unkrass