Using multiple parameters along with a "get in or equal" query in Moodle-CodePudding

I am new to Moodle and trying to construct a query for Moodle that uses the WHERE x IN (list of ids)

From the docs I found the method

$DB->get_in_or_equal($id_list);

Following the documentation I created my query as follows

list($insql, $inparams) = $DB->get_in_or_equal($some_ids);

$sql = "SELECT ...
    FROM a 
    LEFT OUTER JOIN b ON a.id = b.aid AND b.userid = :userid
    WHERE a.id $insql";

$rows = $DB->get_records_sql($sql, $inparams);

My question is on how to get the :userid param added in the method $DB->get_records_sql(...)

The Moodle documentation mentions using named params but the example was not very clear.

Would appreciate it if anyone has any insight on how to construct a query like this (i.e. a query that use WHERE ... IN and other params)

Thanks in advance.

CodePudding user response：

Try:

[$insql, $params] = $DB->get_in_or_equal($someids, SQL_PARAMS_NAMED);
$sql = "
   SELECT ...
     FROM a
     JOIN b ON a.id = b.aid AND b.userid = :userid
    WHERE a.id $insql";
$params['userid'] = $someuserid;
$rows = $DB->get_records_sql($sql, $params);

The SQL_PARAMS_NAMED option means that the generated $insql will look something like "IN (:param1, :param2, :param3)" rather than "IN (?, ?, ?)". You can use either, but you can't mix and match within a single query e.g. you can have:

"a.id IN (?, ?, ?) AND b.userid = ?", with the values in the params array in the correct order
OR "a.id IN (:param1, :param2, :param3) AND b.userid = :userid", with the params array keys named to match (but they can be in any order)

I generally use the second version, whenever there is more than one param to insert, as you're less likely to make a mistake when refactoring end up with the params inserted in the wrong order.