mysql - SQL Moodle 使用自定义配置文件字段

Question

我创建了 2 个用户配置文件字段员工编号(简称:employeeid)组织(简称:组织)

这是我的代码:

SELECT 
        MAX(CASE WHEN f.shortname = 'employeeid' THEN uid.data ELSE '' END) AS 'Employee No.',
    CONCAT(u.firstname,' ',u.lastname) AS 'Full Name',
    CONCAT('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',u.id,'">', u.username,'</a>') AS 'User Name',
        MAX(CASE WHEN f.shortname = 'Organisation' THEN uid.data ELSE '' END) AS 'Organisation',
    c.fullname AS 'Course',
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date',
DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(p.timecompleted),INTERVAL 1 YEAR), '%m/%d/%Y') AS 'Expiration Date' 

FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field f ON uid.fieldid = f.id

WHERE f.shortname IN ('employeeid', 'Organisation') IS NOT NULL

%%FILTER_COURSES:Course%%
%%FILTER_USERS:uid.data%%
%%FILTER_SEARCHTEXT:uid.data:~%%
GROUP BY u.id

表中的结果非常好，但每当我应用我拥有的过滤器(例如:按组织过滤，这是一个下拉列表)时，它会删除员工列中的数据，但它仍会显示其他数据其他列。

如果我使用针对员工 ID 的搜索文本过滤器，组织中的数据将会消失。

有什么办法可以避免吗？请帮助。谢谢。

PS:我正在使用可配置报告在 Moodle 中创建 SQL 查询

Answer 1

您可能会在以下方面取得更大的成功:

SELECT 
    employee.data AS 'Employee No.',
CONCAT(u.firstname,' ',u.lastname) AS 'Full Name',
CONCAT('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',u.id,'">', u.username,'</a>') AS 'User Name',
    org.data AS 'Organisation',
c.fullname AS 'Course',
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date',
DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(p.timecompleted),INTERVAL 1 YEAR), '%m/%d/%Y') AS 'Expiration Date' 

FROM prefix_course_completions p
JOIN prefix_cours c ON p.course = c.id
JOIN prefix_user u ON p.userid = u.id
LEFT JOIN (
   SELECT d.userid, d.data
     FROM prefix_user_info_data d
     JOIN prefix_user_info_field f ON f.id = d.fieldid AND f.shortname = 'employeeid'
) employee ON employee.userid = u.id
LEFT JOIN (
   SELECT d.userid, d.data
     FROM prefix_user_info_data d
     JOIN prefix_user_info_field f ON f.id = d.fieldid AND f.shortname = 'Organisation'
) org ON org.userid = u.id

WHERE 1=1

%%FILTER_COURSES:Course%%
%%FILTER_USERS:employee.data%%
%%FILTER_SEARCHTEXT:org.data:~%%