How to use Char_Length IN Dql

Question

What i need :

• i need to arrange the order text in descending order.

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);

• i have refer the link :https://gist.github.com/smottt/1615966.

• 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.

• please tell where i have done wrong. char_length nor length is working but doc says its support length http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#dql-functions
• any ideas are most welcome.

Answer 1

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