Moodle 中的查询参数数量不正确 *如何从课程页面获取外部链接

Question

亲爱的 我尝试在 Moodle 中运行以下临时数据库查询，但出现错误，

有什么方法可以修复它，谢谢

*我的目标是从课程页面查找所有外部链接 (URL)

SELECT
        concat('<a target="_new" href=%%WWWROOT%%/course/view.php?id=',
                c.id, '">', c.fullname, '</a>') AS Course
        ,c.shortname,r.name
        ,(
            SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
            FROM prefix_role_assignments AS ra
                JOIN prefix_context AS ctx ON ra.contextid = ctx.id
                JOIN prefix_user AS u ON u.id = ra.userid
            WHERE ra.roleid = 3 
            AND ctx.instanceid = c.id 
            LIMIT 1
          ) AS Teacher
        ,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',
                r.id, '">', r.name, '</a>') AS Resource
FROM prefix_resource AS r
    JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'https://stackoverflow.com/%'    

错误消息：

" Error when executing the query: ERROR: Incorrect number of query 
parameters. Expected 2, got 0"

Answer 1

在Moodle中执行SQL语句时，任何“？”字符用于指示需要随查询一起提供的参数的占位符。

根据您的查询风格，我假设您正在使用 report_customsql 或 block_configurablereports 进行查询，因此您无法选择提供此类参数。

如果您查看此处的文档：https://docs.moodle.org/en/Custom_SQL_queries_report 您将看到解决此问题的方法是替换任何“？”查询中包含“%%Q%%”的字符。

所以固定查询应该如下所示：

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php%%Q%%id=',c.id,'">',c.fullname,'</a>') AS Course,
    c.shortname,r.name, (SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher,
concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php%%Q%%id=',r.id,'">',r.name,'</a>') AS Resource
FROM prefix_resource AS r
JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'https://stackoverflow.com/%'