please some one can suggest me a SQL query to find a
List of courses using a particular
Block/assignment/activity/resources (i.e. discussions with no of posts)
Thanks in advance for any help.
In reply to D patel
Re: How to find List of courses using a particular Block/assignment/activity/resources
by oliver stearn -
How about something like this? (done for the book activity)
$allcourseids = $DB->get_records_sql('SELECT DISTINCT course FROM mdl_book WHERE timecreated > ?', array(123456));
$course_count = count($allcourseids);
foreach ($allcourseids as $record){
$course_id = $record->course;
//you can then do something with this course id (like look up and print the course name, construct a link to the course etc)
} #
Sorry my formatting got messed up. You need to reference a different table for each type of activity/resource, so you will need an additional database call to find the course name. On the plus side you have plenty of other activity related data in that allcourseids object. In retrospect not a good variable name as it actually has all the data, not just the id's.
In reply to D patel
Re: How to find List of courses using a particular Block/assignment/activity/resources
by Mary Cooch -
Does it have to be an SQL query? You can find out from the Manage activities and Manage blocks pages in Site admin.
In reply to Mary Cooch
Re: How to find List of courses using a particular Block/assignment/activity/resources
by D patel -
Thanks for reply Mary, Yes i need only a SQL Query to get this details as i am not a Moodle Developer I am a Business Intelligence Developer and working for some as hoc Reporting requirement.
In reply to D patel
Re: How to find List of courses using a particular Block/assignment/activity/resources
by erika alarcon -
I use this query for Blocks
SELECT mdl_course.id as courseID, mdl_course.fullname, mdl_course.shortname, mdl_block_instances.blockname
FROM (mdl_context mdl_context
INNER JOIN mdl_block_instances mdl_block_instances ON (mdl_context.id = mdl_block_instances.parentcontextid))
INNER JOIN mdl_course mdl_course ON (mdl_course.id = mdl_context.instanceid)
WHERE (mdl_block_instances.blockname = 'BLOCK_NAME') AND (mdl_context.contextlevel = 50);