Page Comparison

...

In addition the get_records* and get_recordset* functions provide arguments for OFFSET/LIMIT which should be used instead of including them in the query:

$first10users = $DB->get_records_sql("SELECT * FROM {user} ORDER BY id", null, 0, 10);

$first10users = $DB->get_records_sql("SELECT * FROM {user} ORDER BY id LIMIT 0,10");

Use sql_like() correctly

The syntax for sql_like can be easy to get wrong. Make sure:

• The second argument to sql_like() is either ‘?’ or ‘:named’ only. No % symbols etc, put them in the parameter.
• Always escape any variables in the parameter with $DB->sql_like_escape().

For example do this:

$field = 'fullname';
$word_to_match = 'banana';
$like_sql = $DB->sql_like($field, '?');
$params = ['%' . $DB->sql_like_escape($word_to_match) . '%'];
$DB->get_records_sql("SELECT * FROM {table} WHERE $like_sql", $params);

instead of this:

$field = 'fullname';
$word_to_match = 'banana';
$like_sql = $DB->sql_like($field, '%?%');
$params = array($word_to_match);
$DB->get_records_sql("SELECT * FROM {table} WHERE $like_sql", $params);

Lenient use of whitespace PostgreSQL

If you develop with postgres, make sure you test in another db, because postgres will let you get away with things like this:

SELECT * FROM table WHERE course = 3ORDER BY id; // no space between 3 and ORDER BY

SELECT COUNT (DISTINCT field) FROM table; // space between count and open bracket

This kind of error can happen when you build SQL from strings and forget to include whitespace.

...

PostgreSQL doesn’t support the use of double quotes when quoting a fixed string.

So use single quotes:

SELECT 'mystring';

instead of doing this:

SELECT "mystring";

Fixed values in GROUP BY

PostgreSQL doesn’t support the use of fixed values in a GROUP BY statement:

SELECT 'mystring', COUNT(id) FROM {user} GROUP BY 'mystring';

You will need to refactor your query to avoid grouping by a fixed value.

...

MySQL doesn’t require you specify them all, postgreSQL PostgreSQL does.

SELECT u.firstname,u.lastname,count(u.id) FROM {user} u GROUP BY u.firstname, u.lastname;

SELECT u.firstname,u.lastname,count(u.id) FROM {user} u GROUP BY u.firstname;

This often comes up when grouping by all fields in a table via table.*.

...

The field name used for calculated fields can be different, so always use a field alias so you can be sure how to refer to the field. So use:

SELECT firstname, MIN(id) AS minid FROM {user} GROUP BY firstname;

instead of:

SELECT firstname, MIN(id) FROM {user} GROUP BY firstname;

Don’t use DISTINCT() with table wildcards

This is not supported in mysql:

SELECT DISTINCT(table.*) FROM …

If you exclude the brackets it is okay in mysql:

SELECT DISTINCT table.* FROM …

Don’t use UPDATE FROM SELECT

This is not supported in mysql:

UPDATE table … FROM (SELECT …);

Use the following format for complex UPDATEs

UPDATE {primary_table}
SET field = (SELECT otheralias.otherfield
               FROM {other_table} otheralias
              WHERE {primary_table}.id = otheralias.fk)
WHERE somefield = something
AND EXISTS (SELECT 1
              FROM {anothertable} anotheralias
             WHERE {primary_table}.id = anotheralias.fk);

The important things to note are:

...

Prior to Totara LMS 2.6, MSSQL didn’t support comparison of TEXT fields (Oracle has similar restrictions). Because comparison is used by ORDER BY, DISTINCT and GROUP BY you couldn't use any of these operations on TEXT fields directly:

SELECT id FROM {course} WHERE summary = :summarystring;

SELECT DISTINCT(summary) FROM {course};

SELECT id FROM {course} ORDER BY summary;

SELECT summary, MIN(startdate) FROM {course} GROUP BY summary;

SELECT fullname, COUNT(summary) FROM {course} GROUP BY fullname;

If you really needed to compare them you can use sql_compare_text() or sql_order_by_text():

$sql = "SELECT id FROM {course} WHERE " . $DB->sql_compare_text('summary') . " = :summarystring";

Also be aware that they are just truncating the TEXT fields to a fix length and casting to VARCHAR type so you are not really comparing the whole field when you do this.

...

Oracle only supports a maximum of 1000 items in an IN() clause. If your IN clause is likely to be very large you are probably better off using a JOIN instead (which will probably also be faster).

For example, do:

$sql = "SELECT id FROM {course} WHERE " . $DB->sql_compare_text('summary') . " = :summarystring";

instead of:

SELECT firstname, lastname FROM {user} u
    WHERE id IN (SELECT userid FROM {course_completion} WHERE course = 4);

If you do need to work around it you can use sql_sequence() in totara/core/utils.php which will split large in clauses into IN (…) OR IN (…) OR …

...

Postgres expects that field is of the same type as defaultvalue. MySQL will happily accept field of type int and string in the defaultvalue.

SELECT COALESCE(integerfield, 0);

SELECT COALESCE(varcharfield, "''");

SELECT COALESCE(integerfield, "''");

Using the AS keyword

Different databases have different support for how AS should be used:

...

Recent versions of Moodle require Postgres 9+ and don't support Oracle so technically either approach is supported. However due to previous support for postgres < 8.4 and Oracle, existing queries all use the same convention, so we should stick with it for consistency. This is to use the AS keyword to alias columns, but not tables/subqueries. e.g.:

SELECT id AS userid FROM {user} u;

SELECT id userid FROM {user};

SELECT id FROM {user} AS u;

Huge recordsets

As of Totara 13 you can use get_huge_recordset_xxx() methods for huge recordsets.

...