[SOLVED] MYSQL Query in Calc Elements

[marcq]

Hi,

I have created the following Mysql query in order to sum an element :

$id= '{thistable}.id';
$db = JFactory::getDbo();
$query= "SELECT gprh_fabrik_user_enrollment.id, gprh_fabrik_user_training_124_repeat.prix
FROM (gprh_fabrik_user_enrollment LEFT JOIN gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id) LEFT JOIN gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id
WHERE (((gprh_fabrik_user_enrollment.id)= " . $db->quote($id)"))";
$db->setQuery($query);
$total = $db->loadResult();
return $total;

I've tested the query in PHPMYADMIN :

SELECT gprh_fabrik_user_enrollment.id, gprh_fabrik_user_training_124_repeat.prix FROM (gprh_fabrik_user_enrollment LEFT JOIN gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id) LEFT JOIN gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id WHERE (((gprh_fabrik_user_enrollment.id)= 4))

and the query is returning the good values :

id | prix
4 | 450.00
4 | 2000.00

The problem is the way I get the Id of the Form which is certainly wrong. I tried this :

$id= '{thistable}.id';

but doesn't seems to work.

I would appreciate if someone could check my query.

Cheers,

Marc

 

[marcq]

I tried this, but without success :

$id= '{gprh_fabrik_user_enrollment___id_raw}';
$db = JFactory::getDbo();
$query= "SELECT gprh_fabrik_user_enrollment.id, Sum(gprh_fabrik_user_training_124_repeat.prix)
FROM (gprh_fabrik_user_enrollment LEFT JOIN gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id) LEFT JOIN gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id
GROUP BY gprh_fabrik_user_enrollment.id
HAVING (((gprh_fabrik_user_enrollment.id)= " . $db->quote($id)"))";
$db->setQuery($query);
$total = $db->loadResult();
return $total;

Query is working fine in PHPMyAdmin and is returning the correct value (sum).

Can't figure out where the problem is.

 

[cheesegrits]

The first problem I see is you are missing the string concatenating . after the $db->quote(). Also you are selecting two fields, with the first being the id, but loadResult() will only load the first field in the first row of the the result set.

Here's a rewrite of that, using the query builder rather than raw SQL, and using variable names less likely to conflict with any in use within the code this is eval'ed from:

$enrollmentId= '{gprh_fabrik_user_enrollment___id_raw}';
if (!empty($enrollmentId)) {
   $enrollmentId = is_array($enrollmentId) ? $enrollmentId[0] : $enrollmentId;
   $myDb = FabrikWorker::getDbo();
   $myQuery = $myDb->getQuery(true);
   $myQuery
      ->select('SUM(gprh_fabrik_user_training_124_repeat.prix) AS prix_sum')
      ->from('gprh_fabrik_user_enrollment')
      ->leftJoin('gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id')
      ->leftJoin('gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id')
      ->group('gprh_fabrik_user_enrollment.id')
      ->having('gprh_fabrik_user_enrollment.id = ' . $myDb->quote($enrollmentId));
   $myDb->setQuery($myQuery);
  // var_dump((string)$myQuery); // uncomment this if you want to see your query
   return $myDb->loadResult();
}
return '';

Just for reference, here is the J! API for the query builder:

https://api.joomla.org/cms-3/classes/JDatabaseQuery.html

... and here is a good basic tutorial on it ...

https://docs.joomla.org/Selecting_data_using_JDatabase

-- hugh

 

[marcq]

The first problem I see is you are missing the string concatenating . after the $db->quote(). Also you are selecting two fields, with the first being the id, but loadResult() will only load the first field in the first row of the the result set.

Here's a rewrite of that, using the query builder rather than raw SQL, and using variable names less likely to conflict with any in use within the code this is eval'ed from:

$enrollmentId= '{gprh_fabrik_user_enrollment___id_raw}';
if (!empty($enrollmentId)) {
   $enrollmentId = is_array($enrollmentId) ? $enrollmentId[0] : $enrollmentId;
   $myDb = FabrikWorker::getDbo();
   $myQuery = $myDb->getQuery(true);
   $myQuery
      ->select('SUM(gprh_fabrik_user_training_124_repeat.prix) AS prix_sum')
      ->from('gprh_fabrik_user_enrollment')
      ->leftJoin('gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id')
      ->leftJoin('gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id')
      ->group('gprh_fabrik_user_enrollment.id')
      ->having('gprh_fabrik_user_enrollment.id = ' . $myDb->quote($enrollmentId));
   $myDb->setQuery($myQuery);
  // var_dump((string)$myQuery); // uncomment this if you want to see your query
   $return = $myDb->loadResult();
}
return '';

Just for reference, here is the J! API for the query builder:

https://api.joomla.org/cms-3/classes/JDatabaseQuery.html

... and here is a good basic tutorial on it ...

https://docs.joomla.org/Selecting_data_using_JDatabase

-- hugh

Thank you Hugh, I will take time to study the two references you kindly gave to me.

I have inserted your query into my form Calc Element, I checked the query table and field names everything seems to be correct, but no returned value is displayed below the table (should be 2450).
All "More" Tab parameters are set to no.
Access parameters are correct.
I don't have a clue what's wrong.

 

[cheesegrits]

I'm now slightly confused.

When you say "no returned value is displayed below the table" I presume you are referring to element calculations, where you want to sum all the values of a given element. Are you trying to sum the results of your calc? In other words, is that calc element supposed to return a value for the sum of each row, then you want to sum the total of those? Or are you trying to write a query which just sums all the rows in one go?

The issue with trying to use element calculations to sum a calc element is, if you are adding a calc to a table with existing data, your calc results will not yet be in the actual table. Calc elements only store results in the table when you save the form. So if you add a calc to a form that has existing data, you either then have to edit and save each row to trigger the calc to save the value to the table, or you have to manually populate the values by running a query by hand in phpMyAdmin (or whatever), like "UPDATE mytable SET mycalc = (SELECT SUM(...) etc)"

-- hugh

 

[marcq]

I'm now slightly confused.

When you say "no returned value is displayed below the table" I presume you are referring to element calculations, where you want to sum all the values of a given element. Are you trying to sum the results of your calc? In other words, is that calc element supposed to return a value for the sum of each row, then you want to sum the total of those? Or are you trying to write a query which just sums all the rows in one go?

The issue with trying to use element calculations to sum a calc element is, if you are adding a calc to a table with existing data, your calc results will not yet be in the actual table. Calc elements only store results in the table when you save the form. So if you add a calc to a form that has existing data, you either then have to edit and save each row to trigger the calc to save the value to the table, or you have to manually populate the values by running a query by hand in phpMyAdmin (or whatever), like "UPDATE mytable SET mycalc = (SELECT SUM(...) etc)"

-- hugh

Sorry Hugh, I wasn't clear.

I don't know if you recall, you helped me for two day to create a databasejoin elements in order to display training courses and sessions. The purpose was to enable candidates to enroll for specific training courses and sessions by clicking the appropriate checkboxes. And finally submit in order to keep the data in the database (Form : Inscription formation).

I have replicated the list/form in order to enable the course administrator to have the list and details of the people who add enroll for specific training courses and sessions (Form : Inscription formation en attente).

I would like to sum the total of those training courses and sessions the candidate have applied/enrolled for (should be only those who have been checked).

By the way I would also like to display only the training courses and sessions for which the candidate applied/enrolled for.

I apologize for my English, which also need some improvement, but I hope it is clear for you now.

Cheers,

Marc

 

[cheesegrits]

Can I get phpMyAdmin access?

-- hugh

 

[marcq]

Can I get phpMyAdmin access?

-- hugh

Thank you Hugh, please find all needed information into my "Site Name & Notes" in the "Notes" Textarea.

 

[cheesegrits]

OK, should be working now. I'd missed a little issue when I rewrote the code, I wasn't returning the result. I've corrected the code in post #3.

I've re-edited and saved your four test rows, so the values are now stored in the table, and the 'sum' is working for that column as well. I've also set "Calc on save only", so it only runs that query when you submit the form, not when you display the list, so it just uses the stored value on display. That saves a lot of unnecessary queries when rendering the list.

-- hugh

 

[marcq]

OK, should be working now. I'd missed a little issue when I rewrote the code, I wasn't returning the result. I've corrected the code in post #3.

I've re-edited and saved your four test rows, so the values are now stored in the table, and the 'sum' is working for that column as well. I've also set "Calc on save only", so it only runs that query when you submit the form, not when you display the list, so it just uses the stored value on display. That saves a lot of unnecessary queries when rendering the list.

-- hugh

Hugh, thank you for your always outstanding support. I appreciate. Cheers, Marc.