Last active
August 25, 2024 18:22
-
-
Save aelfannir/b1c0a75d7bc5139331c52097d0070c26 to your computer and use it in GitHub Desktop.
LIMIT in DQL subquery
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # config/packages/doctrine.yaml | |
| doctrine: | |
| # ... | |
| orm: | |
| #... | |
| dql: | |
| #... | |
| string_functions: | |
| FIRST: App\Doctrine\DQL\FirstFunction | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| namespace App\Doctrine; | |
| use Doctrine\ORM\Query\AST\ASTException; | |
| use Doctrine\ORM\Query\AST\Functions\FunctionNode; | |
| use Doctrine\ORM\Query\AST\Subselect; | |
| use Doctrine\ORM\Query\Parser; | |
| use Doctrine\ORM\Query\QueryException; | |
| use Doctrine\ORM\Query\SqlWalker; | |
| use Doctrine\ORM\Query\TokenType; | |
| /** | |
| * FirstFunction ::= FIRST" "(" Subselect ")" | |
| */ | |
| class FirstFunction extends FunctionNode | |
| { | |
| private Subselect $subselect; | |
| /** | |
| * {@inheritdoc} | |
| * @throws QueryException | |
| */ | |
| public function parse(Parser $parser): void | |
| { | |
| $parser->match(TokenType::T_IDENTIFIER); | |
| $parser->match(TokenType::T_OPEN_PARENTHESIS); | |
| $this->subselect = $parser->Subselect(); | |
| $parser->match(TokenType::T_CLOSE_PARENTHESIS); | |
| } | |
| /** | |
| * {@inheritdoc} | |
| * @throws ASTException | |
| */ | |
| public function getSql(SqlWalker $sqlWalker): string | |
| { | |
| return '(' . $this->subselect->dispatch($sqlWalker) . ' LIMIT 1)'; | |
| } | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| namespace App\Repository; | |
| use Doctrine\ORM\EntityManager; | |
| class ProductRepository | |
| { | |
| private EntityManager $entityManager; | |
| public function __construct(EntityManager $entityManager) | |
| { | |
| $this->entityManager = $entityManager; | |
| } | |
| public function findProductWithHighestCurrentPrice(): ?Product | |
| { | |
| $qb = $this->entityManager->createQueryBuilder(); | |
| $subQuery = $this->entityManager->createQueryBuilder(); | |
| $subQuery->select('IDENTITY(pp.product)') | |
| ->from('App\Entity\ProductPrice', 'pp') | |
| ->where('pp.isCurrentPrice = :isCurrentPrice') | |
| ->orderBy('pp.amount', 'DESC') | |
| ->setParameter('isCurrentPrice', true); | |
| $qb->select('p') | |
| ->where( | |
| $qb->expr()->eq( | |
| 'p.id', | |
| $qb->expr()->literal( | |
| sprintf('FIRST(%s)', $subQuery->getDQL()) | |
| ) | |
| ) | |
| ) | |
| ->setMaxResults(1) | |
| ->setParameters($subQuery->getParameters()); | |
| return $qb->getQuery()->getOneOrNullResult(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment