CODEWITHSUNDEEP
moodle
elunap
elunap

Reputation: 841

How to create complex sql queries in moodle

Hellow, I have this problem, I don't know how to create complex queries on moodle, I read the documentation about data manipulation and nothing worked for me.

This is the query I want to get:

SELECT mdl_pilar.nombrepil, mdl_punto.texto as pregunta, mdl_audidet.texto as comentario, mdl_audidet.resultado 
FROM mdl_audidet, mdl_pilar, mdl_punto 
WHERE mdl_audidet.idaud='1' and mdl_audidet.idpunto=mdl_punto.idpunto and mdl_audidet.idtipoaud='1' AND 
mdl_pilar.idpilar=(SELECT mdl_pilar.idpilar FROM mdl_pilar WHERE mdl_punto.idpilar=mdl_pilar.idpilar);

I would really appreciate the help, thank you.

Upvotes: 0

Views: 122

Answers (1)

Russell England
Russell England

Reputation: 10241

Here is a tidier version of the query

SELECT pi.nombrepil,
        pu.texto AS pregunta,
        a.texto AS comentario,
        a.resultado 
FROM mdl_audidet a
JOIN mdl_punto pu ON pu.idpunto = a.idpunto
JOIN mdl_pilar pi ON pi.idpilar = pu.idpilar
WHERE a.idaud = '1'
AND a.idtipoaud = '1'

In Moodle you would use something like this

$sql = "SELECT pi.nombrepil,
                pu.texto AS pregunta,
                a.texto AS comentario,
                a.resultado 
        FROM {audidet} a
        JOIN {punto} pu ON pu.idpunto = a.idpunto
        JOIN {pilar} pi ON pi.idpilar = pu.idpilar
        WHERE a.idaud = :idaud
        AND a.idtipoaud = :idtipoaud";

$params = array('idaud' => '1', 'idtipoaud' => '1');

$records = $DB->get_records_sql($sql, $params);

or use this if you are expecting a single result:

$record = $DB->get_record_sql($sql, $params);

Upvotes: 1

Related Questions