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

Russell England · Accepted Answer · 2016-09-27 16:48:26Z

2

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);

answered Sep 27, 2016 at 16:48

Russell England

10.3k2 gold badges30 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related