One more SQL query inside a function

John Evergrey
Topic Author
Offline
Junior Member

14 years 8 months ago - 14 years 8 months ago #1 by John Evergrey

One more SQL query inside a function was created by John Evergrey

Hi guys,

I am currently blending Joomdle with Highslide and Highcharts and I was just wondering whether I can add another query inside existing one.

Here is the code of the function get_user_grade (I want to use it inside this function):

Code:

function get_user_grades ($user,$cid) 
	{
        global $CFG, $DB;

		$user = utf8_decode ($user);
        $user = get_complete_user_data ('username', $user);
        $uid = $user->id;


		$SQL = "SELECT g.itemid, g.finalgrade,gi.courseid,gi.itemname,gi.id, g.timecreated, g.feedback
                      FROM {$CFG->prefix}grade_items gi
                           JOIN {$CFG->prefix}grade_grades g      ON g.itemid = gi.id
                           JOIN {$CFG->prefix}user u              ON u.id = g.userid
                           JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id
                       WHERE g.finalgrade IS NOT NULL
			   AND u.id =  ?
			   AND gi.courseid = ?
                  GROUP BY g.itemid
                  ORDER BY g.timecreated DESC";
     
                           
	    $sum_array = array();
		$params = array ($uid, $cid);
	    if ($sums = $DB->get_records_sql($SQL, $params)) 
		{
			$i = 0;
			$rdo = array ();
			foreach ($sums as $sum)
			{
				 if (! $grade_grade = grade_grade::fetch(array('itemid'=>$sum->id,'userid'=>$uid))) {
					$grade_grade = new grade_grade();
					$grade_grade->userid = $this->user->id;
					$grade_grade->itemid = $grade_object->id;
					
          }

					$grade_item = $grade_grade->load_grade_item();

					$sums2[$i] = $sum;
					$scale = $grade_item->load_scale();
					$formatted_grade = grade_format_gradevalue($sums2[$i]->finalgrade, &$grade_item, true, GRADE_DISPLAY_TYPE_REAL);
          $sums2[$i]->finalgrade = $formatted_grade;
								 
					$rdo[$i]['itemname'] = $sum->itemname;
					$rdo[$i]['finalgrade'] = $formatted_grade;
          $rdo[$i]['timecreated'] = $sum->timecreated;
          $rdo[$i]['feedback'] = $sum->feedback;
			    $rdo[$i]['itemid'] = $sum->itemid;        
          $i++;
			}
			return $rdo;
			return $sums2;
		}

    
		return array();
    }

I have added several items to display as well.

Now I want to count the amount of individual grades. I am using scale with only 5 grades (1, 2, 3, 4 and 5) and I wanna know how many 1s are there etc.

I want to somehow add this query:

Code:

SELECT COUNT(prefix_grade_grades.finalgrade) as count
FROM prefix_grade_grades
WHERE prefix_grade_grades.finalgrade = 5.00000

But I want to use it separately from the finalgrade which is used in the original query. I don't want these two values to interfere. So is it possible to squeeze this code inside the original SQL or should I make $SQL2 with the code? If so what other changes should I make to the additional code? I hope I made myself clear. If not, I can clarify further.

Thank you for your answers
V.

EDIT: And one quick question. When I display the feedback, is it somehow possible to display the feedback which contains HTML? When the feedback is written in the Moodle editor, there are tags like <p> etc. but then the feedback is not displayed and I get an API error, the only way is to manually delete the tags and view it as ugly plain text. Can I make the tags working?

Last edit: 14 years 8 months ago by John Evergrey. Reason: Another Q

Please Log in or Create an account to join the conversation.

Antonio Durán
Offline
Moderator

14 years 8 months ago #2 by Antonio Durán

Replied by Antonio Durán on topic Re: One more SQL query inside a function

Hi.

It depends on what you need to do ( I don't fully understand the goal).

So, if you are to return more info about each user grade for a course, say:
$rdo[$i] = $other_thing.

You can use $sql2, get the info and merge it in the original reply, like you did with feedback, etc.

If you need different info, because you are doing totals and stuff, I think it would be better to create a new web service function, to return just that info.

As for the HTML problem: I am not sure why it happens. Course description also contains html and it renders properly, so take a look at it and look for any differences with your code.

Please Log in or Create an account to join the conversation.

John Evergrey
Topic Author
Offline
Junior Member

14 years 8 months ago #3 by John Evergrey

Replied by John Evergrey on topic Re: One more SQL query inside a function

Thank you for the answer. I have the feedback working, all I needed to do was change the data type to RAW instead of TEXT, so perfect

.

Here is more information about my project. However the more I think about it I am not sure whether it will work due to safety and security reasons of Moodle.

This is my layout of overview of grades from English language:

Next to every grade items there three icons. First is a chart, second is the feedback and the third is a file from teacher. The content of each icon will popup with Highslide effect (this is already working).

Now for the chart. It will look like this:

There will be always 5 bars because I use scale with only 5 grades. And this is what I want:

I want to calculate using the database to tell how many users (students) had grade 1 from this assignment, how many had grade 2 etc. Is it clearer now?

Please Log in or Create an account to join the conversation.

Antonio Durán
Offline
Moderator

14 years 8 months ago #4 by Antonio Durán

Replied by Antonio Durán on topic Re: One more SQL query inside a function

It looks really nice

Yes, it is clear now: you can do it both ways:

* Add it to current function. I think it is better, to reduce overhead.

Just make the query inside the tasks loop, and add the result to the $rdo[$i] array.

* Create a new webservice to return that info.
Something like:
assignment_get_grade_total ($assignment_id, $grade)
[$grade = 1, 2, 3, 4, 5]

You will need to create it in auth.php, db/services.php, helpers/externallib.php

Then, you would call it for every grade from Joomla.

Please Log in or Create an account to join the conversation.

John Evergrey
Topic Author
Offline
Junior Member

14 years 8 months ago #5 by John Evergrey

Replied by John Evergrey on topic Re: One more SQL query inside a function

Thanks

Okey, I think I am half way there. But I am not sure where I should put the query and what to actually do with it after it.

Here is what I have:

Code:

$sql ="SELECT COUNT({$CFG->prefix}grade_grades.finalgrade) as count
            FROM {$CFG->prefix}grade_grades
            WHERE {$CFG->prefix}grade_grades.finalgrade = 5.00000";

This is the query. And I have inserted it in here between those:

Code:

if (! $grade_grade = grade_grade::fetch(array('itemid'=>$sum->id,'userid'=>$uid))) {
					$grade_grade = new grade_grade();
					$grade_grade->userid = $this->user->id;
					$grade_grade->itemid = $grade_object->id;
					
          }
          $sql ="SELECT COUNT({$CFG->prefix}grade_grades.finalgrade) as count
            FROM {$CFG->prefix}grade_grades
            WHERE {$CFG->prefix}grade_grades.finalgrade = 5.00000";
            
					$grade_item = $grade_grade->load_grade_item();

					$sums2[$i] = $sum;

Thought it could be easy and tried to call it like this:

Code:

$rdo[$i]['count'] = $sql->count;

The page will load but the chart want appear.

You use this line in the example which I think is to summon the data from $SQL, should I use it as well?

Code:

if ($sums = $DB->get_records_sql($SQL, $params))

Thanks for the answer.

Please Log in or Create an account to join the conversation.

Antonio Durán
Offline
Moderator

14 years 8 months ago #6 by Antonio Durán

Replied by Antonio Durán on topic Re: One more SQL query inside a function

It would be something like:

$sql ="SELECT COUNT({$CFG->prefix}grade_grades.finalgrade) as count
FROM {$CFG->prefix}grade_grades
WHERE {$CFG->prefix}grade_grades.finalgrade = 5.00000";

$n_5 = $DB->get_records_sql($sql);
$rdo[$i] = $n_5;

Of course, you will need to adapt the webservice definition, like you did to include feedback.

Please Log in or Create an account to join the conversation.

John Evergrey
Topic Author
Offline
Junior Member

14 years 8 months ago - 14 years 8 months ago #7 by John Evergrey

Replied by John Evergrey on topic Re: One more SQL query inside a function

Thank you for the answer. I have tried that and I am receiving this error:

XML-RPC Error (0): Invalid external api response: n5 (Scalar type expected, array or object received.)

So I think there will be problem with the webservice definition in the externallib.php.

Now I have it like this:

'n_5' => new external_value(PARAM_INT, 'n_5'),

But I think there should be something instead of PARAM_INT. But since I am not a programmer I am not sure. What do you think?

EDIT: When I edit this line:

$rdo[$i] = $n_5;

To this: $rdo[$i] = $n_5->n_5;

No more error, but the chart won't display.

Last edit: 14 years 8 months ago by John Evergrey. Reason: More findings

Please Log in or Create an account to join the conversation.

Antonio Durán
Offline
Moderator

14 years 8 months ago #8 by Antonio Durán

Replied by Antonio Durán on topic Re: One more SQL query inside a function

Yes, the edit is well done.

I don't know why your chart won't display, but that seems to be working (it is correclty returning an integer, as the xmlrpc error is gone).

Please Log in or Create an account to join the conversation.

John Evergrey
Topic Author
Offline
Junior Member

14 years 8 months ago #9 by John Evergrey

Replied by John Evergrey on topic Re: One more SQL query inside a function

Yes, no more error. But when I try to display the value in the normal view and not in the chart, there is nothing displayed. Maybe there is truly a problem with Moodle security which doesn't allow me to see that data.

Please Log in or Create an account to join the conversation.

Antonio Durán
Offline
Moderator

14 years 8 months ago #10 by Antonio Durán

Replied by Antonio Durán on topic Re: One more SQL query inside a function

I don't think so.

But programming web services is a tricky task, as they are not easy to debug.

You can remove the mods, and start putting them in place one by one, until you find the one that breaks it.

Please Log in or Create an account to join the conversation.

Login

One more SQL query inside a function