Moodle自定义用户配置字段关联SQL合并开发咨询
Moodle 自定义用户字段关联证书查询SQL整合
核心调整逻辑
- 统一使用Moodle框架标准的
{}表占位符替换原片段中硬编码的prefix_前缀,适配不同站点的自定义表前缀配置 - 修正原自定义字段查询片段的语法问题:补全子查询左括号、修复字段别名处单双引号不匹配的错误
- 把子查询的关联条件从原片段的
UID.userid = U.id调整为匹配主查询的用户表别名UID.userid = u.id,确保跨表关联逻辑正确 - 将原逗号连接的
user_info双表写法替换为标准JOIN关联,和Moodle核心代码的查询风格保持一致,不会引入性能问题
完整可直接运行的SQL
$sql = "SELECT ci.id, u.id as userid, $userfields, co.id as courseid, co.fullname as coursefullname, c.id as certificateid, c.name as certificatename, c.verifyany, -- 嵌入member_status字段查询 ( SELECT UID.data FROM {user_info_field} UIF JOIN {user_info_data} UID ON UID.fieldid = UIF.id WHERE UIF.shortname = 'member_status' AND UID.userid = u.id ) as member_status FROM {customcert} c JOIN {customcert_issues} ci ON c.id = ci.customcertid JOIN {course} co ON c.course = co.id JOIN {user} u ON ci.userid = u.id WHERE ci.code = :code";
结果读取说明
- 整合后的SQL无需调整原有结果解析逻辑,查询返回的每条记录可以直接通过
$record->member_status读取对应用户的会员状态值 - 若部分用户未配置
member_status自定义字段,子查询会返回NULL,不会阻断主查询的正常结果输出
内容的提问来源于stack exchange,提问作者Jeff Olivo
相关产品推荐
相关产品推荐

