[SOLVED] COUNT DISTINCT using Query->newExpr()?

Need Help
1

I have a custom finder that add some count columns with QueryExpressions, but I couldn’t find a way to add “DISTINCT” clause to those expressions.
Here is an extract of the finder

$baseExpr = $query->newExpr()
    ->eq('DocumentTypes.entidad_id', 1)
    ->add($query->newExpr()->isNotNull('DocumentTypes.id'))
    ->add($query->newExpr('IF(Providers.relation = 1, DocumentTypes.applyDep = 1, DocumentTypes.applyAuto = 1)'))
    ;
return $query->select([
    'required_approved' => $query->func()->count(
        $query->newExpr()->addCase(
            (clone $baseExpr)
                ->add($query->newExpr()->eq('DocumentTypes.required', 1))
                ->add($query->newExpr()->eq('Documents.approved', 2)),
            new IdentifierExpression('DocumentTypes.id')
        )
    ),
    'required_pending' => $query->func()->count(
        $query->newExpr()->addCase(
            (clone $baseExpr)
                ->add($query->newExpr()->eq('DocumentTypes.required', 1))
                ->add($query->newExpr()->eq('Documentos.approved', 1)),
            new IdentifierExpression('DocumentTypes.id')
        )
    ),
    // multiple columns with different conditions
]);

this generates the following sql

SELECT
  (
    COUNT(
      (
        CASE WHEN (
          DocumentTypes.entity_id = 1
          AND (DocumentTypes.id) IS NOT NULL
          AND IF(
            Providers.relation = 1, DocumentTypes.applyDep = 1,
            DocumentTypes.applyAuto = 1
          )
          AND DocumentTypes.required = 1
          AND Documents.approved = 1
        ) THEN DocumentTypes.id END
      )
    )
  ) AS `required_approved`,
  -- .....
FROM ....

Now the only way to build a distinct clause is like this

return $query->select([
    'required_approved' => $query->func()->count(
        $query->func()->DISTINCT(
            [
                $query->newExpr()->addCase(
                    (clone $baseExpr)
                        ->add($query->newExpr()->eq('DocumentTypes.required', 1))
                        ->add($query->newExpr()->eq('Documents.approved', 2)),
                    new IdentifierExpression('DocumentTypes.id')
                )
            ],
            ['literal'],
            ['integer']
        )
    ),
    // ...
);

but the resulting query throws a syntax error

SELECT (
    COUNT((  -- double parens breaking sql
        DISTINCT((
            CASE WHEN (
              DocumentTypes.entity_id = 1
              AND (DocumentTypes.id) IS NOT NULL
              AND IF(
                Providers.relation = 1, DocumentTypes.applyDep = 1,
                DocumentTypes.applyAuto = 1
              )
              AND DocumentTypes.required = 1
              AND Documents.approved = 1
            ) THEN DocumentTypes.id END
        ))
    ))
) AS `required_approved`,
-- ....
FROM ....

It just throws error
Error: 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 ‘DISTINCT((CASE WHEN (DocumentTypes.entity_id = 1 AND (DocumentTypes.id) IS NO’ at line 1

Because It adds double parenthesis to the count function, I need it to remove it.
Is it possible? Am I doing it wrong? Is there another way?

2

Solved via PR #11410