I’ve found a solution by adapting this DQL example to DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query: Important it’s the createQueryBuilder of connection not the one of the entity manager. $subSelect … Read more
In addition to the accepted answer there are a tonne of pre-built custom functions available at https://github.com/beberlei/DoctrineExtensions . These can be then registered in your config like doctrine: orm: dql: string_functions: DATE: DoctrineExtensions\Query\Mysql\Date and can then be used in your DQL (as in your query) like DATE(jobs.endDate) AS endDate
Based on the original alswer of ChrisR inspired in Marco Pivetta’s post I’m adding here the solution if you’re using Symfony2: Looks like Symfony2 doesn’t use the original Doctrine command at: \Doctrine\ORM\Tools\Console\Command\SchemaTool\UpdateCommand Instead it uses the one in the bundle: \Doctrine\Bundle\DoctrineBundle\Command\Proxy\UpdateSchemaDoctrineCommand So basically that is the class that must be extended, ending up in having: … Read more
You can use the Criteria API to filter the collection: <?php use Doctrine\Common\Collections\Criteria; class Article { /** * @ORM\OneToMany(targetEntity=”Comment”, mappedBy=”article”) */ protected $comments; public function getComments($showPending = false) { $criteria = Criteria::create(); if ($showPending !== true) { $criteria->where(Criteria::expr()->eq(‘approved’, true)); } return $this->comments->matching($criteria); } } This is especially nice, because Doctrine is smart enough to only … Read more
You could also force partial objects, to get rid off lazy-loading: use Doctrine\ORM\Query; //… $query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
You can write a join DQL query as below $em = $this->getContainer()->get(‘doctrine’)->getManager(); $repository = $em->getRepository(‘YourNamespaceYourBundle:User’); $query = $repository->createQueryBuilder(‘u’) ->innerJoin(‘u.groups’, ‘g’) ->where(‘g.id = :group_id’) ->setParameter(‘group_id’, 5) ->getQuery()->getResult(); Your mapping for groups property in User entity will handle join part itself you don’t have to mention the junction table in your DQL query
You should be able to add your own function to implement this feature. The class would look something like this: namespace MyProject\Query; use Doctrine\ORM\Query\AST\Functions\FunctionNode; use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\Parser; use Doctrine\ORM\Query\SqlWalker; class CastAsInteger extends FunctionNode { public $stringPrimary; public function getSql(SqlWalker $sqlWalker) { return ‘CAST(‘ . $this->stringPrimary->dispatch($sqlWalker) . ‘ AS integer)’; } public function parse(Parser $parser) … Read more
Configure it in config.yml: doctrine: # … orm: # … naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
As stated by the Doctrine Configuration Reference by default logging of the SQL connection is set to the value of kernel.debug, so if you have instantiated AppKernel with debug set to true the SQL commands get stored in memory for each iteration. You should either instantiate AppKernel to false, set logging to false in you … Read more
Doctrine now has Criteria which offers a single API for filtering collections with SQL and in PHP, depending on the context. https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/working-with-associations.html#filtering-collections Update This will achieve the result in the accepted answer, without getting everything from the database. use Doctrine\Common\Collections\Criteria; /** * @ORM\Entity */ class Member { // … public function getCommentsFiltered($ids) { $criteria = … Read more