SQL and MoodleForm query

SQL and MoodleForm query

by Jordan Kirby -
Number of replies: 2

Hi guys,

I am working on a report plugin for Moodle, the main purpose of which is to allow for the user to 'nail down' further into the log files already provided by Moodle.

The input is in the form of 4 dropdown boxes, as follows:

  1. Report Type (this is the main part, where the user can select a report such as 'Course Views' / 'Resource Views' / 'Forum Posts')
  2. From date
  3. To date (this and the above obviously work in conjunction to allow the user to select a date range from the beginning of the course to the current date)
  4. User (this selection is either All Users / Selection of one user enrolled within the course)

 Obviously the SQL query run is dependent on the selection of these - the way I see it, for using 'Course Views' as an example, will be by using something similar to the following:

SELECT time, user, course

FROM {log}

WHERE module = 'course' AND action = 'view'

 I understand this is very dumbed down SQL, though I've just come to a relative standstill on the two points below:

  • Is there an added benefit in using a moodleform as opposed to html_writer::?
  • Is querying the mdl_log table suitable or going to balloon the time it takes to process (the reason I've chosen it is that it already provides a great level of detail)

 

The output when the user clicks a 'Submit' button once selecting from the dropdown boxes, will be a table of data resulting from the query that matches their input.

After this, I want to take this data and visualise it, preferably using the D3.js library, however that's quite a way off right now.

 

Sorry it's quite a longwinded question, I've just been shifting through masses of code from core plugins to third-party ones and they have many different ideas and ways of implementation, so I thought I'd try and pick the minds of people here who could give me a push in the right direction.

 

Many thanks,

Jordan

Average of ratings: -
In reply to Jordan Kirby

Re: SQL and MoodleForm query

by Ankit Agarwal -

Hi Jordan,

Before I answer your queries I invite you to have a look at our new events and logging infrastructure:-
http://docs.moodle.org/dev/Event_2

http://docs.moodle.org/dev/Logging_2

Please note that the logging interfaces are not finalised at this point and still under development. These changes are going to make the logs much richer and flexible.

Here is response to your queries:-

Is querying the mdl_log table suitable or going to balloon the time it takes to process (the reason I've chosen it is that it already provides a great level of detail)

Avoid using the log table. Since this table would be supported only for legacy logs 2.7 onwards. Instead you can use the new logging interface to get logs and build your report around it. We would have an api something like get_events() where you can easily filter the logs you want by component/user/type of action/ read or write actions etc. I would suggest you wait a couple of weeks, until we finalise the specs on the new logging interfaces and put up a prototype for public comments.

Is there an added benefit in using a moodleform as opposed to html_writer::?

The biggest advantage in my opinion is custom validations and security. On top of that there are lot of other additional adminstrative advantages, such as setting default values, freezing fields and much more. You can go through our docs (http://docs.moodle.org/dev/Form_API) for a detail explanations of what you can do with moodleforms.

Hope that helps
Cheers

Average of ratings: -
In reply to Ankit Agarwal

Re: SQL and MoodleForm query

by Jordan Kirby -

Hi Ankit,

Many thanks for the reply. Really interesting stuff there, especially about the new events and logging infrastructure.

I've had a good look through and it seems great, however the only worry is about the date that it will be ready for development use. Unfortunately, I have a deadline to complete this project implementation by the end of January (preferably) at the latest.

 I'll have a further look at the new infrastructure, however if its release is too far away, I may have to do some testing with the existing mdl_log table, with the hope of updating this for the release of the new infrastructure. Am I right in presuming that by 'legacy logs 2.7' you refer to the upcoming 2.7 version of Moodle, with legacy logs being the current ones in use?

Thanks for the information of the form usage too, I think I'll go ahead as planned and make use of the moodleform.

Best,

Jordan

Average of ratings: -