Ad-hoc database queries: Problems with Link in Query

Ad-hoc database queries: Problems with Link in Query

by Georg Gruber -
Number of replies: 5

Hello,

we're using Moodle 3.11.4 and the latest release of ad-hoc Report 4.1.

We want to show a link in the report with the text Link. Right now we are using the folling SELECT statement:

CONCAT("%%WWWROOT%%/mod/assign/view.php%%Q%%id=",cm.id, "&action=grading") AS "Link"

This states shows:

https://path.to.websiteroot/mod/assign/view.php?id=1360&action=grading

as a link and we can open it. But what we want is:

Link

and this should be clickable to open the selected page. I#ve read in the documentation that the folling statement should work:

CONCAT('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php%%Q%%id=',cm.id, '&action=grading">Link</a>') AS "Link"

Unfortunately this is not showing as a link. Instead the report shows it as plain text:
<a target="_blank" href=".....

Does anybody have a clue why the html tag is a plain text? By the way: in a sql query this is working....

Thanks

Average of ratings: -
In reply to Georg Gruber

Re: Ad-hoc database queries: Problems with Link in Query

by Randy Thornton -
Picture of Documentation writers

The Ad-hoc database queries has its own peculiar method of constructing URL links, as it mentions in its Notes where you build the query. You have to use its method: you don't put in any HTML code in the CONCAT statements to make the links. It builds the HTML for you. Otherwise, as you notice, it just spits out the link in full as plain text.

Please see this section in the Ad-hoc contributed reports page on how this works including sample code.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Ad-hoc database queries: Problems with Link in Query

by Georg Gruber -
Thank you. That helped a lot.

Regards
Average of ratings: -
In reply to Georg Gruber

Re: Ad-hoc database queries: Problems with Link in Query

by Randy Thornton -
Picture of Documentation writers
Most welcome. Glad that was helpful.

It gave me the chance to finally document how it works. That method makes creating links much quicker than having to fiddle with the html and quotes, etc. But it is non-standard.
Average of ratings: -
In reply to Randy Thornton

Re: Ad-hoc database queries: Problems with Link in Query

by Vicke Denniston -
Picture of Testers
This is excellent, I figured out my query.
Thank you
Average of ratings: -
In reply to Randy Thornton

Re: Ad-hoc database queries: Problems with Link in Query

by Vicke Denniston -
Picture of Testers
I spoke too soon. Is there any way to work a DISTINCT statement in there? I need only 1 result for each of the linked values.
SELECT
u.id AS 'URL id',
/*DISTINCT*/ CONCAT("%%WWWROOT%%/url/view.php%%Q%%id=1010171", cm.module) AS "link module" ,
u.course,
cm.course,
c.shortname,
u.name AS 'url name',/*u.intro AS 'URL intro',
u.externalurl,*/
cm.module
FROM
prefix_url AS u
JOIN prefix_course AS c
ON u.course = c.id
JOIN prefix_course_modules AS cm
ON cm.course = c.id

WHERE u.externalurl LIKE '%ensemble.viterbo%'
Average of ratings: -