Generico Dataset Help with MySQL query

Generico Dataset Help with MySQL query

by Ron Griffith -
Number of replies: 3


I am not a developer even though I am a snippet user. and I am getting familar with bootstrap and I have been trying to create 2 separate Generico templates one for each of the following queries that will simply display the result ( a number) of the query. Moodle 3.3.5+ (Build: 20180405)

This query simply counts the number of completed courses.

SELECT

  COUNT(*)

FROM mdl_certificate_issues

  WHERE userid = CURRENT_USER


This query shows the total number of credits the user has completed. (I use the certificate plugin and rely on the printhours field in mdl_certificate)

SELECT SUM(printhours)

  FROM mdl_certificate

  INNER JOIN mdl_certificate_issues

    ON mdl_certificate.id = mdl_certificate_issues.certificateid

WHERE mdl_certificate_issues.userid = CURRENT_USER

I have tried everything, I am frazzled, I think that there are a couple things missing or formatted wrong? After looking around and lots of trial and error I think that the MySQL SUM function cannot be used here because of the format $DB->get_records_sql call function does not allow it? However, I do see a reference to COUNT here... https://docs.moodle.org/dev/Data_manipulation_API and I'm not sure how to use it.  OR I could be wrong and just be using the wrong syntax in the dataset. I have listed below all the not empty settings in my first template Course Count.


Autofill template with a Preset --custom--

The key that identifies template: coursecount 

Template Name: Course Count

The body of template:

My User ID is @@CERTIFICATE_ISSUES:userid@@ in the mdl_certificates_issues table. 

(I guess this doesn't work because it returns more than one value?)

@@USER:id@@ <-- value here proves generico is working keep looking.

What I would like to see here is...

Total Courses Completed: @@CERTIFICATE_ISSUES:COUNT(*)@@

Dataset: "SELECT COUNT(*) FROM {certificate_issues} WHERE userid = ?;"


Dataset Variables: @@USER:id@@, @@CERTIFICATE_ISSUES:COUNT(*)@@

I am thinking that course count can be fixed with some changes to the dataset setting?


The Second query with SUM(printhours) I'm not sure will work here? So my thought was to create a table on the fly that can be used as a dataset? where the query creates the table to be used in the dataset, 

The other thought was creating a .php file and going that route but I don't know php but could piece together whats needed after looking at some tutorials but my thought was these two simple query should be a perfect fit for using in generico and this would give me some new functions with informing the students how many credits and courses they have completed. 


Average of ratings: -
In reply to Ron Griffith

Re: Generico Dataset Help with MySQL query

by Justin Hunt -
Picture of Particularly helpful Moodlers Picture of Plugin developers

I think you probably did not have enough examples to go on really. These two templates should be better examples for what you are trying to do. The two comments I would make:

i) The SQL goes in the dataset field. But to get Generico  variables into the SQL you need to use the Dataset variables field.


ii) To access the content of the returned dataset, you use this syntax @@DATASET:completions@@ . In this case "completions" is the field from the returned dataset that we are interested in.


If you have more than one record in the returned dataset, its actually a bit of a song and a dance to get at the data. And you will need to do that from the customjs field.

Average of ratings: -
In reply to Justin Hunt

Re: Generico Dataset Help with MySQL query

by Ron Griffith -

THANK YOU!!!!!

That information was perfect and was just what I needed I have been able to create many templates now! I have one more question and I don't know if it is simple or not. How do I have a "0" as the default fill in? Let me ask it another way, how can I have a zero appear as the default when there is no data to show?

This is the most awesome and powerful plugin I have used for moodle so far. The possibilities are endless! Thank you for your work!

Average of ratings: -
In reply to Ron Griffith

Re: Generico Dataset Help with MySQL query

by Justin Hunt -
Picture of Particularly helpful Moodlers Picture of Plugin developers

Ron, 

If you are using an SQL count function, then it should return '0' if no records match your query. 

If you need to use branching logic, based on the results, eg 

if( @@DATASET:count@@ ==0){
  //do something
}else{
 //do something else
}

Then you should really use custom JS. It is possible to get clever with CSS and hide/show things depending on the value of a variable without JS. But customJS is going to be easier to maintain into the future and offers more advantages.



Average of ratings: -