General developer forum

How to find List of courses using a particular Block/assignment/activity/resources

 
Picture of D patel
How to find List of courses using a particular Block/assignment/activity/resources
 

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. 


 
Average of ratings: -
Picture of oliver stearn
Re: How to find List of courses using a particular Block/assignment/activity/resources
 
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.

 
Average of ratings: -
Mary Cooch
Re: How to find List of courses using a particular Block/assignment/activity/resources
Documentation writersMoodle Course Creator Certificate holdersMoodle HQParticularly helpful MoodlersTestersTranslators

Does it have to be an SQL query? You can find out from the Manage activities and Manage blocks pages in Site admin.

 
Average of ratings: -
Picture of D patel
Re: How to find List of courses using a particular Block/assignment/activity/resources
 

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.

 
Average of ratings: -
Picture of erika alarcon
Re: How to find List of courses using a particular Block/assignment/activity/resources
 

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);


 
Average of ratings: Useful (1)