Reputation: 845
What i need :
Here is my dql query:
$from='Entities\EventOrganizer eo';
$qb = $this->em->createQueryBuilder();
$qb->select('eo.metadata ')
->add('from', $from)
->where('eo.event = '.$this->event->getId())
->andWhere('eo.edition='.$this->event->getEventEdition()->getId())
->andWhere('eo.organizerType=5')
->andwhere("eo.entityType='U'")
->andwhere("eo.published=1")
->orderBy('char_length(eo.metadata)','desc');
$query = $qb->getQuery();
$result = $query->getResult();
echo $query->getSQl();
$this->cache->SaveObject($str, $result);
according the link i have made changes in the config.yml file
entity_managers:
default:
dql:
numeric_functions:
char_length:
Acme\bundlename\Extension\Doctrine\Query\Mysql\CharLength.
Error im getting:
PHP Fatal error: Uncaught exception 'Doctrine\\ORM\\Query\\QueryException'
with message 'SELECT eo.metadata FROM Entities\\EventOrganizer eo WHERE eo.event
= 127632 AND eo.edition=10 AND eo.organizerType=5 AND eo.entityType='U' AND
eo.published=1 ORDER BY length(eo.metadata) desc' in /home/ind/public_html/serve-bizt-
com/application/libraries/Doctrine/ORM/Query/QueryException.
Upvotes: 2
Views: 2119
Reputation: 2512
the function you are looking for is
LENGTH()
and it would work like this for your query
$from='Entities\EventOrganizer eo';
$qb = $this->em->createQueryBuilder();
$qb->select('eo.metadata, LENGTH(eo.metadata) AS HIDDEN len')
->add('from', $from)
->where('eo.event = '.$this->event->getId())
->andWhere('eo.edition='.$this->event->getEventEdition()->getId())
->andWhere('eo.organizerType=5')
->andwhere("eo.entityType='U'")
->andwhere("eo.published=1")
->orderBy('len','desc');
$query = $qb->getQuery();
$result = $query->getResult();
echo $query->getSQl();
$this->cache->SaveObject($str, $result);
HIDDEN is important if you don't want to parse the results ;)
But try to implement a better query, in the EO Repository
$query_builder = $this->createQueryBuilder('eo')
->select('eo.metadata, LENGTH(eo.metadata) AS HIDDEN len')
->where('eo.event = :event_id')
->andWhere('eo.edition = :event_edition_id')
->andWhere('eo.organizerType=5')
->andwhere("eo.entityType='U'")
->andwhere("eo.published=1")
->orderBy('len','desc')
->setParameter('event_id', $this->event->getId())
->setParameter('event_edition_id',$this->event->getEventEdition()->getId();
$result = $query_builder->getQuery()->getResult();
http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#dql-functions
Upvotes: 2