[MSSQL] Invalid query generation when using GroupBy with a ResultVariable #6165

New Issue

admin · 2026-01-22T15:28:04+01:00

admin commented

2026-01-22 15:28:04 +01:00

Originally created by @KevVerF on GitHub (Feb 1, 2019).

Quite some years ago, support was added to use GroupBy based on a ResultVariable => 2642daa438 .

However, this doesn't always works on MSSQL as MS has its own syntax ;(. For example following query will fail on MSSQL.

SELECT DISTINCT 
                          p.scientificNameID AS species_id,
                          p.scientificName AS species,
                          p.match_search AS search_species_name,
                          p.match_near AS near_match_species,
                          (CONCAT(genus.match_near, ' ', p.match_near)) AS near_match_gen_sp,
                          (CONCAT(genus.scientificName, ' ', p.scientificName)) AS genus_species
                        FROM %s p
                          JOIN p.parent genus
                          WHERE genus.scientificNameID = :genus_id
                            AND p.match_len BETWEEN :species_len_start AND :species_len_end
                        GROUP BY p.scientificNameID, p.scientificName, p.match_search, p.match_near, near_match_gen_sp, genus_species
                        ORDER BY p.scientificName

As this generates:

SELECT DISTINCT t0_.id AS id_0,
         t0_.tu_name AS tu_name_1,
         t0_.match_search AS match_search_2,
         t0_.match_near AS match_near_3,
         ((t1_.match_near + ' ' + t0_.match_near)) AS sclr_4,
         ((t1_.tu_name + ' ' + t0_.tu_name)) AS sclr_5
   FROM tu t0_ INNER JOIN tu t1_ ON t0_.tu_parent = t1_.id 
   WHERE t1_.id = ? AND t0_.match_len BETWEEN ? AND ? 
   GROUP BY t0_.id, t0_.tu_name, t0_.match_search, t0_.match_near, sclr_4, sclr_5 
   ORDER BY t0_.tu_name ASC

Therefore, I have a request to make this (optionally) platform specific. I went through the code a bit to locate the part of interest and my best guess would be to modify Doctrine\ORM\Query\SqlWalker::walkGroupByItem().

// ResultVariable
        if (isset($this->queryComponents[$groupByItem]['resultVariable'])) {
            $resultVariable = $this->queryComponents[$groupByItem]['resultVariable'];

            if ($resultVariable instanceof AST\PathExpression) {
                return $this->walkPathExpression($resultVariable);
            }

            if (isset($resultVariable->pathExpression)) {
                return $this->walkPathExpression($resultVariable->pathExpression);
            }
            // *********************************
            // Something like this could be done
           if ($handler = $this->conn->getDatabasePlatform()->getGroupByResultVariableHandler()) {
              return $handler->walkResultVariable(groupByItem); // Off course with additional parameters with query info so the expression itself could be repeated for the mssql case. 
            }
            // *********************************
            return $this->walkResultVariable($groupByItem);
        }

Unfortunately I'm not familiar with the doctrine code else I would have made a pull request rather than copy/pasting some code here. So I might have missed some practices, issues, etc...

Thanks in advance!

Originally created by @KevVerF on GitHub (Feb 1, 2019). Quite some years ago, support was added to use GroupBy based on a ResultVariable => https://github.com/doctrine/orm/commit/2642daa43851878688d01625f272ff5874cac7b2 . However, this doesn't always works on MSSQL as MS has its own syntax ;(. For example following query will fail on MSSQL. ``` sql SELECT DISTINCT p.scientificNameID AS species_id, p.scientificName AS species, p.match_search AS search_species_name, p.match_near AS near_match_species, (CONCAT(genus.match_near, ' ', p.match_near)) AS near_match_gen_sp, (CONCAT(genus.scientificName, ' ', p.scientificName)) AS genus_species FROM %s p JOIN p.parent genus WHERE genus.scientificNameID = :genus_id AND p.match_len BETWEEN :species_len_start AND :species_len_end GROUP BY p.scientificNameID, p.scientificName, p.match_search, p.match_near, near_match_gen_sp, genus_species ORDER BY p.scientificName ``` As this generates: ``` sql SELECT DISTINCT t0_.id AS id_0, t0_.tu_name AS tu_name_1, t0_.match_search AS match_search_2, t0_.match_near AS match_near_3, ((t1_.match_near + ' ' + t0_.match_near)) AS sclr_4, ((t1_.tu_name + ' ' + t0_.tu_name)) AS sclr_5 FROM tu t0_ INNER JOIN tu t1_ ON t0_.tu_parent = t1_.id WHERE t1_.id = ? AND t0_.match_len BETWEEN ? AND ? GROUP BY t0_.id, t0_.tu_name, t0_.match_search, t0_.match_near, sclr_4, sclr_5 ORDER BY t0_.tu_name ASC ``` Therefore, I have a request to make this (optionally) platform specific. I went through the code a bit to locate the part of interest and my best guess would be to modify Doctrine\ORM\Query\SqlWalker::walkGroupByItem(). ``` php // ResultVariable if (isset($this->queryComponents[$groupByItem]['resultVariable'])) { $resultVariable = $this->queryComponents[$groupByItem]['resultVariable']; if ($resultVariable instanceof AST\PathExpression) { return $this->walkPathExpression($resultVariable); } if (isset($resultVariable->pathExpression)) { return $this->walkPathExpression($resultVariable->pathExpression); } // ********************************* // Something like this could be done if ($handler = $this->conn->getDatabasePlatform()->getGroupByResultVariableHandler()) { return $handler->walkResultVariable(groupByItem); // Off course with additional parameters with query info so the expression itself could be repeated for the mssql case. } // ********************************* return $this->walkResultVariable($groupByItem); } ``` Unfortunately I'm not familiar with the doctrine code else I would have made a pull request rather than copy/pasting some code here. So I might have missed some practices, issues, etc... Thanks in advance!

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6165