SQL: Dynamic table names for course modules in a sql query

SQL: Dynamic table names for course modules in a sql query

by David North -
Number of replies: 4

I'm not sure if this is possible. I am trying to improve a BI Dashboard plugin I have made, trying to minimise the amount of processing power and DB calls it makes.


Currently to get the activity instance record I do this

      

$sql = "SELECT cm.id, cm.instance, m.name 
 FROM {course_modules} cm
 INNER JOIN {modules} m ON m.id = cm.module
 WHERE m.name <> 'label' AND cm.id IN (".$course_activities.")";

$course_modules = $DB->get_records_sql($sql, null);

foreach ($course_modules as $cmkey => $course_module) {

    $activity_instance = $DB->get_record($course_module->name, array('id'=>$course_module->instance));
.....
.....
}


Is there a way that I can include the activity instance in the first query. ie.

Something like:

$sql = "SELECT cm.id, cm.instance, m.name, a.name
FROM {course_modules} cm
INNER JOIN {modules} m ON m.id = cm.module
INNER JOIN {m.name} a ON a.id = cm.instance
WHERE m.name <> 'label' AND cm.id IN (".$course_activities.")";


Average of ratings: -
In reply to David North

Re: SQL: Dynamic table names for course modules in a sql query

by Michael Aherne -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Hi David, no that sort of thing isn't possible in SQL. It sounds like you could probably get the data you're looking for using get_fast_modinfo(), though - have you tried that? You may well get the data from the cache, so it wouldn't hit the database at all.

Average of ratings: Useful (1)
In reply to Michael Aherne

Re: SQL: Dynamic table names for course modules in a sql query

by David North -
Thanks Michael. No, I didn't know about get_fast_modinfo(), it's very helpful!
Average of ratings: -
In reply to David North

Re: SQL: Dynamic table names for course modules in a sql query

by Phil Lello -

More generically, and assuming direct DB access is needed, you could build one query in a loop (whether hitting the DB directly is better or worse than using the caching functions will depend on whether or not you mostly hit or miss cache).

For example (pseudo-code!):

$config = array('<tablename>' => '<fieldname>', ...);

$sql = array();

foreach ($config as $table => $field)

{

    $sql[] = "SELECT {$table}.$field  AS <commonname> FROM {$table} WHERE ....";

}

$sql = implode($sql, " UNION ");

$DB->get_XXX($sql, ...);

I've used this approach when needing to get counts of activities with a given deadline, as the deadline field is a per-activity-type one not per-activity, and the relevant field name changes between types.

Phil

Average of ratings: -