SQL help needed to incorporate filter into query

SQL help needed to incorporate filter into query

by Stephanie Worden -
Number of replies: 4
I'm using this report as my starting point: (https://docs.moodle.org/310/en/ad-hoc_contributed_reports#Site-Wide_Grade_Report_with_All_Items), and the query works, but the filter I added in Moodle had no effect on the report.  From another forum, I learned that I need to edit the code to incorporate the filter into the query for it to have any effect.  I tried checking the documentation for this plugin to figure out what I need to add to the SQL code.

I added these lines:
     WHERE 1=1
     %%FILTER_SEARCHTEXT:u.username%%
in the code below, but can't seem to get the syntax right.  Moodle keeps telling me that I have an error and need to check the manual that corresponds to my version of MariaDB, but I'm not sure where to find what to find my version information.  How do I find this information, and what am I doing wrong?

SELECT u.firstname AS 'First' , u.lastname AS 'Last',
CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',  
u.username AS 'Username',
c.fullname AS 'Course',
 
CASE
  WHEN gi.itemtype = 'course'
   THEN ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',
 
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS Time

WHERE 1=1
%%FILTER_SEARCHTEXT:u.username%%

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE  gi.courseid = c.id
ORDER BY lastname

Average of ratings: -
In reply to Stephanie Worden

Re: SQL help needed to incorporate filter into query

by Randy Thornton -
Picture of Documentation writers
The FROM clause must immediately follow the SELECT clause, and it it must be immediately followed by the WHERE clause. You have two WHERE clauses, one stuck in the middle in the wrong place, and you can only have one, after the FROM. See: https://www.tutorialspoint.com/mysql/mysql-select-query.htm.

You need to move the filter from the first WHERE clause to the second WHERE clause to create one single clause.
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: SQL help needed to incorporate filter into query

by Stephanie Worden -
I missed the second WHERE clause. Thanks!

This is the original report I got from the ad hoc page, and it works, even though the FROM clause is separated from the SELECT clause,so I guess I'm confused:

SELECT u.firstname AS 'First' , u.lastname AS 'Last',
u.firstname + ' ' + u.lastname AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
WHEN gi.itemtype = 'course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') AS TIME

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category

WHERE gi.courseid = c.id
ORDER BY lastname

Here is the code as I've edited it. It's mostly the same, but I cannot get Moodle to execute the filter:
SELECT u.firstname AS 'First' , u.lastname AS 'Last',
CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',
u.username AS 'Username',
c.fullname AS 'Course',

CASE
WHEN gi.itemtype = 'course'
THEN ' Course Total'
ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category

WHERE gi.courseid = c.id
%%FILTER_SEARCHTEXT:u.username%%
ORDER BY lastname
Average of ratings: -
In reply to Stephanie Worden

Re: SQL help needed to incorporate filter into query

by Randy Thornton -
Picture of Documentation writers
Stephanie,

Try this for your filter: %%FILTER_SEARCHTEXT:u.username:~%%
There's a squiggly ~ at the end before the second %% - this is the indicator to the filter what to do.

Also, your ORDER BY should have a u.lastname with the u. alias, otherwise it won't know what table you mean.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: SQL help needed to incorporate filter into query

by Stephanie Worden -
Randy,
Thanks. I tried what you suggested, but Moodle's still ignoring the filter.

Stephanie
Average of ratings: -