incorrect algorithm for creating database queries in ProductCommentRepository class

[covox76]

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

There is a flawed algorithm in the methods that generate database queries in the following functions:
getAverageGrades
getCommentsNumberForProducts
They assume that products have sequential IDs. If this is not the case (because products were previously deleted), the code will not work correctly
if ($count - 1 > $index) {
$sql .= ‘,’;

Steps to reproduce

The problem arises when the product list does not have sequential IDs.

The queries generated by the methods will be incorrect due to the absence of commas. Error message:
Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SUM(IF(id_product = 223 AND deleted = 0 AND validate = 1 ,1, 0)) AS "223" FRO...' at line 1 in main/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28

Expected behavior

In file productcomments\src\Repository\ProductCommentRepository.php in line 325 and 385
the code should be more secure, and the lines
$sql .= ',1, 0)) AS "' . $esqID . '"';

        if ($count - 1 > $index) {
            $sql .= ',';
        }
    }

should look something like this:
$sql .= ',1, 0)) AS "' . $esqID . '",';
}

    $sql = rtrim($sql, ",");

Actual Result

No response

PrestaShop version where the bug happens

8.2.5, 9.1.0

Module version where the bug happens

8.0.0

PHP version(s) where the bug happened

8.1.34,

Your company or customer's name goes here (if applicable).

No response

[metineren]

This error occurs because the array keys for $productIds are not in sorted order. Sorting the array before starting the foreach loop resolves the issue.

I submitted a pull request to resolve this issue.

[metineren]

Ping @kpodemski