General developer forum

Useful SQL Queries?

 
Picture of Ruthine Burton
Re: Useful SQL Queries?
 

I managed to write the query to find pdfs, but would welcome any comments on better ways of writing this. It may also be useful to anyone else who gets a request like this.

The code:

SELECT

    prefix_files.filename AS PDF,

    prefix_course.fullname AS Course,

CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=', prefix_course.id,'">link</a>') AS 'Link To Course',

   prefix_course_modules.course     AS 'Course id'

FROM prefix_files

INNER JOIN prefix_context 

ON prefix_files.contextid = prefix_context.id

INNER JOIN prefix_course_modules

ON prefix_context.instanceid = prefix_course_modules.id

INNER JOIN prefix_course

ON prefix_course_modules.course = prefix_course.id

WHERE (prefix_files.filename LIKE '%pdf%')

GROUP BY prefix_course.id

 
Average of ratings: -
Picture of Eoin Campbell
Re: Useful SQL Queries?
Core developersParticularly helpful MoodlersPlugin developers

Just to note that you don't need to use CHAR(63) instead of the question mark '?' in your query. I noticed in a sample query in the Moodle documentation at Custom SQL queries report, it uses %%Q%% instead. I've tested and it works, so it makes code a little bit simpler. Instead of:

CONCAT('<a href="%%WWWROOT%%/course/view.php',CHAR(63),'id=', prefix_course.id,'">link</a>')

you can use:

CONCAT('<a href="%%WWWROOT%%/course/view.php%%Q%%id=', prefix_course.id,'">link</a>')
 
Average of ratings: -