How can I create a SQL query that only shows courses that have moved into a particular category within the current month?

How can I create a SQL query that only shows courses that have moved into a particular category within the current month?

by Matthew Willis -
Number of replies: 1

I have a query that shows the course creation date and the category they're in, but I can't see anything in mdl_logstore_standard_log, mdl_course or mdl_course_categories that I can use to create filter on when they were moved. When moving the course to another category, it creates this entry in the logstore_standard_log table:

 

The query I'm running is a simple one: 
select cat.name, c.* from mdl_course c
   join mdl_course_categories cat on cat.id = c.category
      and cat.path = '/15/' or cat.path like '/15/%'
order by 
   cat.name asc,
   c.fullname asc

I want this query only to show courses that were moved into this categoryid within the current month. I know I can filter the log table on the timemodified column on each log entry (i.e. and date_format(from_unixtime(c.timemodified), '%Y-%m') = date_format(now(), '%Y-%m')) to show the courses that have been edited within the current month, but this doesn't capture the courses moving between categories.


Does anyone have a solution for this they're willing to share? Much appreciated in advance!

Average of ratings: -
In reply to Matthew Willis

Re: How can I create a SQL query that only shows courses that have moved into a particular category within the current month?

by Randy Thornton -
Picture of Documentation writers
Matthew,

The log event that this creates is one where the eventname is "\core\event\course_updated" and the action is "updated" as well. The target of course is "course". You can filter on that in your code.

That's the best you can do with the logs since "updated" can mean many things, only one of which is moving a course to another category. It's doesn't specify. But it will at least narrow the results down some.
Average of ratings: Useful (1)