0

This is my table test_sum_expression with columns:

uid (int) Stores the UID of the user.
points (int) Stores a number.

enter image description here

I have this SQL sentence which SUMS all points from user with uid 1.

SELECT uid, SUM(points) AS total_points 
FROM `test_sum_expression` 
WHERE uid = 1
GROUP BY UID;

Which produces
enter image description here

How can I produce this same sentence in DBTNG (Database: The Next Generation) format? I mean something like this: $query = $connection->select('test_sum_expression', 'n')...

I have tried this

$query = $connection->select('test_sum_expression', 'n')
  ->fields('n', ['uid', 'points'])
  ->condition('n.uid', 1, '=')
  ->groupBy('n.uid');
$query->addExpression('SUM(n.points)', 'total_points');
$queryPoints = $query->execute();

dpm($queryPoints);

But without success, it produces $queryPoints Drupal\Core\Database\StatementWrapper#456238 (3). I need to be able to get the total_points.

1 Answer 1

1

$query->execute() returns an object that implements StatementInterface. To get the table columns matching that query you need to either loop over that object or call one of the methods defined from that interface.

Since you are only interested to the sum of the points, I would use the following code.

$query = $connection->select('test_sum_expression')
  ->condition('uid', 1);

$query->addExpression('SUM(points)', 'total_points');

$total_points = $query->execute()->fetchField();
2
  • Thanks! Don't forget to declare the alias in the select sentence as it is in the addExpression, it returns an error. I have a question, if I use an alias is it required to use it in the condition and addExpression too? Jul 11, 2023 at 13:30
  • 1
    I am not sure you are referring to the alias associated to the expression. In that case, the alias can be only used in addExpression() as in $select = $this->database->select('node__body', 't'); $select->addExpression('MAX(entity_id)'); $last = $select->execute()->fetchField();. You can use the alias in condition() if the condition needs to check the value of that expression.
    – apaderno
    Jul 11, 2023 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.