CODEWITHSUNDEEP
mysqlsqlmoodle
crbroflovski
crbroflovski

Reputation: 90

SQL query to return all courses inside a subcategory

What's up guys? I'm trying get a sql query which returns some datas. Look at the situation:

I have a set of categories, category unity and organization, that are my main category, inside these categories I have a lot more subcategory of them, e.g. math, dance, etc. inside these subcategories I have some more, e.g. Extension, Graduation, Other, etc. Inside these categories I have some courses that I call here leaf courses, e.g. applied mathematics, and/or I have another subcategories, e.g. math2016.2, math2017.1, etc. and inside can have courses and/or another subcategories. So you can see I might have a lot more of subcategories inside my main categories.

What happens next is, I got 2 queries, in my first one I can return only my leaf courses, in other words, I can return the courses that are directly associated to my subcategory Extension (that is the category I want return all courses inside), but I can't return the courses inside a subcategory inside Extesion, in other words, I can't return courses inside math2016.2 and math2017.1.

In my second query I can return all courses inside the subcategory math/extension/* but the problem is, I have to pass in the query only one subcategory id, in other words, I have to pass the id of the subcategory math in one query and in another query I have to pass the id of the subcategory dance, as I have many subcategories isn't trival do this.

So I guess these queries I have can be transformed to be only one and in only one query I return all courses inside all Extension subcategories, How can I do this?

The queries follows bellow:

1) Return leaf courses of a subcategory Extension

SELECT ct.id, c.fullname, ct.name, ct.path, COUNT(ct.id) AS coursecount
FROM mdl_course_categories ct
INNER JOIN mdl_course c ON c.category=ct.id
WHERE ct.name like "Extension%"
GROUP BY ct.id, ct.name, c.fullname, ct.path
ORDER BY COUNT(ct.id) DESC

2) Returns all courses that are inside of a specific subcategory

SELECT ct.id, c.fullname, ct.name, ct.path, COUNT(ct.id) AS coursecount
FROM mdl_course_categories ct
INNER JOIN mdl_course c ON c.category=ct.id
WHERE ct.path like "/2/36/76%"
GROUP BY ct.id, ct.name, c.fullname, ct.path
ORDER BY COUNT(ct.id) DESC

In the table mdl_course_categories I have the column path, this column has tha path of the subcategory, e.g. Unity is id 2, so the path is /2, math is id 4, then the path is /2/4 (it means math is inside unity), math2017.1 is id 6, so the path is /2/4/5/6

+----+------------+----------+
| id |   course   |   path   |
+----+------------+----------+
|  2 |      unity |       /2 |
|  4 |       math |     /2/4 |
|  5 |  extension |   /2/4/5 |
|  6 | math2017.1 | /2/4/5/6 |
+----+------------+----------+

I also have to say that the subcategory extension has a different id to each category up him, that is, extension inside math can have id 5 but extension inside dance has another id totally different, and has no pattern.

Upvotes: 0

Views: 1933

Answers (2)

Mervyn
Mervyn

Reputation: 61

If you are trying to get the courses in a moodle plugin you can try this:

$categories = coursecat::get(0);
foreach($categories as $category) {
    $courses_in_category = $category->get_courses();//You can pass an array of options also to the method.
    //Process the course array
}

You can also pass a array of options to the get_courses method. Some of options are recursive (returns courses from sub categories as well) , sort etc.

Hope this helps

Upvotes: 1

davosmith
davosmith

Reputation: 6317

You probably want something like this:

SELECT ct.id, c.fullname, ct.name, ct.path
FROM mdl_course_categories ct
JOIN mdl_course_categories basecat ON basecat.id = ct.id OR ct.path LIKE CONCAT(basecat.path, '/%')
JOIN mdl_course c ON c.category = ct.id
WHERE ct.name LIKE 'Extension%'

Notes:

  • This is MySQL only - if you are doing this from within Moodle itself, you can replace the 'CONCAT' part with $DB->sql_concat() for cross-db compatibility (you'd also want to use $DB->sql_like() where possible).
  • From your question, "ct.path LIKE '/2/36/76%'" can go wrong, as this would also match subcategories of '/2/36/761' - which is why my query has "basecat.id = ct.id OR ct.path LIKE CONCAT(basecat.path, '/%')" (comparing directly by ID, then comparing the path with a trailing '/' added).
  • It is not valid to get the name of each course and the number of courses in each category at the same time (you'd need to split the query into subqueries to do this) - either you're grouping by the category and getting a count of courses, or you're not grouping but getting all the courses.

Upvotes: 1

Related Questions