Documentation for Developers - learnweb/moodle-mod_moodleoverflow GitHub Wiki

This Documentation explains classes, functions and features that need a longer explanation than an inline comment can give.

SQL to get unmailed posts in the mail_manager

The mail_manager function moodleoverflow_get_unmailed_posts has a long sql query with a complex structure. Let's break it down, beginning from the center.

  • SELECT * FROM (...) as subscriptions: This part retrieves the user id that are subscribed to a moodleoverflow. There are different ways to be subscribed to a moodleoverflow (forced subscriptions to the whole forum, optional subscriptions to only certain forums or even discussions) and moodleoverflows stores them differently in the databases (forced subscriptions that cannot be undone are stored as a parameter in moodleoverflow table, for optional subscriptions there are two database tables that store the user and the moodleoverflow or the exact discussion). The first part only retrieves the data of the two moodleoverflow subscription tables. For the forced subscriptions a search is needed, where all enrolled users of a course with a moodleoverflow are listed. The UNION-statement combines then all users that are subscribed to a moodleoverflow or a certain forum. A user can be listed several times (for different discussions or moodleoverflows).

  • LEFT JOIN {user} u ON u.id = subscriptions.userid: This appends on every subscribed user all the information about the user. This is completes the information about the mail receiver userto.

  • FROM {moodleoverflow_posts} ... JOIN () cm ON cm.instance = mo.id: Here every posts is retrieved and joined with the existing discussion, moodleoverflow, course and course module. This is used to have all information about the posts enviroment.

  • JOIN {user} author ON author.id = p.userid This joins a posts with its existing author. An author can appear several times.

  • WHERE p.mailed ...: This filters only posts that need to be mailed and that are in the right time span (the time span gets configured in the send_mails() function)

TL;DR: The Query tables returns a list of all unmailed posts together with the information about the author of the post and the user that will receive an email. One row per mail (1 unmailed posts and 5 receivers = 5 rows).

⚠️ **GitHub.com Fallback** ⚠️