Moodle: using mysql joins in $table = set_sql(); method

Question

I want to know how joins are applied for $table= set_sql(); method. I'm trying to render a table without using the conventional html_table(); method.

A basic application for a single database table "mdl_user":

  $table->set_sql('*', "{user}", '1');

But I intend to achieve more complex sql query using joins as shown below:

    **SELECT aa.firstname, aa.email, zz.fullname
    FROM mdl_table1 aa
    INNER JOIN mdl_table2 zz
    ON aa.id = zz.userid WHERE lastlogin => ? and lastlogin <= ? GROUP BY firtname;**

This link below might be helpful. I've tried to work around it but still not clear about it. https://docs.moodle.org/dev/lib/tablelib.php

score 2 · Accepted Answer · answered Sep 27 '16 at 16:48

Something like this

$fields = 'aa.firstname, aa.email, zz.fullname',
$from = '{table1} aa
         INNER JOIN {table2} zz ON aa.id = zz.userid';
$where = 'lastlogin => :lastlogin1 and lastlogin <= :lastlogin2';
$params = array('lastlogin1' => $lastlogin, 'lastlogin2' => $lastlogin);
$table->set_sql($fields, $from, $where, $params);

Moodle: using mysql joins in $table = set_sql(); method

1 Answers1