Development:DB layer 2.0 examples: Difference between revisions
mNo edit summary |
|||
Line 22: | Line 22: | ||
=== Mixing query params and IN params === | === Mixing query params and IN params === | ||
As you know, in Moodle 2.0 we are using placeholders for all the parameters passed to any SQL statement (see [[Development:DB layer 2.0 migration docs#g6|G6]]. Also, we use one special function to convert any list of values to the proper IN/EQUAL clause and associated params (see [[Development:DB layer 2.0 migration docs# | As you know, in Moodle 2.0 we are using placeholders for all the parameters passed to any SQL statement (see [[Development:DB layer 2.0 migration docs#g6|G6]]. Also, we use one special function to convert any list of values to the proper IN/EQUAL clause and associated params (see [[Development:DB layer 2.0 migration docs#g7|G7]]). | ||
But what happens when we want to mix both type of params (query params and IN params) in the same query? Here it's one example: | But what happens when we want to mix both type of params (query params and IN params) in the same query? Here it's one example: |
Revision as of 16:58, 5 May 2009
Note: This page is a work-in-progress. Feedback and suggested improvements are welcome. Please join the discussion on moodle.org or use the page comments.
Template:Development:dmllib 2.0Template:Moodle 2.0
Dropping one enum from one field
In Moodle 2.0, we have discontinued support for ENUM (check constraint) in DB columns. See MDL-18577 about that. So, any plugin using enums in Moodle 1.9 will need to drop them as part of the upgrade to Moodle 2.0. To achieve that, as commented in the migration docs, the drop_enum_from_field() method will be used. Here it's one real example used to drop the enum defined in the forum->type column as part of the upgrade from Moodle 1.9 to 2.0 (just adjust it for your own needs):
/// Dropping all enums/check contraints from core. MDL-18577
if ($result && $oldversion < 2009042700) {
/// Changing list of values (enum) of field type on table forum to none
$table = new xmldb_table('forum');
$field = new xmldb_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'general', 'course');
/// Launch change of list of values for field type
$dbman->drop_enum_from_field($table, $field);
/// forum savepoint reached
upgrade_mod_savepoint($result, 2009042700, 'forum');
}
Mixing query params and IN params
As you know, in Moodle 2.0 we are using placeholders for all the parameters passed to any SQL statement (see G6. Also, we use one special function to convert any list of values to the proper IN/EQUAL clause and associated params (see G7).
But what happens when we want to mix both type of params (query params and IN params) in the same query? Here it's one example:
Objective: return the number of forum discussion per course belonging to a given list of courses (IN params) and one type of forum (query param).
/// Here it's the query param (we are looking for 'general' forums in this example)
$query_params = array('general');
/// Here we calculate the IN clause (the list of courses we are going to search)
/// it returns 2 values: the SQL IN clause needed and the params array to be applied
list($in_sql, $in_params) = $DB->get_in_or_equal(array(1, 2, 3, 4, 5, 6));
/// Here it's the query we are going to execute, with the IN clause injected in place
$sql = "SELECT f.course, COUNT(*) count
FROM {forum} f
JOIN {forum_discussions} d ON d.forum = f.id
WHERE f.type = ?
AND f.course $in_sql
GROUP BY f.course";
/// Here we merge all params (query ones and IN clause ones,
/// respecting the order they are used in the query!)
$params = array_merge($query_params, $in_params);
/// Execute the query, returning the expected count per course
$records = $DB->get_records_sql($sql, $params);