CODEWITHSUNDEEP
symfonydoctrine-orm
shuba.ivan
shuba.ivan

Reputation: 4061

Doctrine Query Builder Length

how to reproduce in query builder this 

AND     LENGTH( CONCAT_WS('', lsc.door_code, lsc.alarm_code, lsc.service_code, lsc.master_log) ) > 0"

I try like this 

            $orX = $qb->expr()->orX();

        $orX
            ->add($qb->expr()->length('lsc.doorCode > 0'))
            ->add($qb->expr()->length('lsc.alarmCode > 0'))
            ->add($qb->expr()->length('lsc.serviceCode > 0'))
            ->add($qb->expr()->length('lsc.masterLog > 0'));

        $qb->andWhere($orX);

have error:

[Syntax Error] line 0, col 549: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '>'

and my query 

SELECT 
            CONCAT(
            IFNULL(location.name, ''),' (',IFNULL(location.streetAddress, ''),' ',IFNULL(location.staircase, ''),' ',IFNULL(location.flatNumber, ''),' ',IFNULL(location.postal, ''),' ',IFNULL(location.postOffice, ''),')') as address,            
            lsc.doorCode,
            lsc.serviceCode,
            lsc.alarmCode,
            lsc.masterLog
         FROM AppBundle:LocationServiceCompany lsc INNER JOIN lsc.location location WHERE lsc.serviceCompany = :sc AND (LENGTH(lsc.doorCode > 0) OR LENGTH(lsc.alarmCode > 0) OR LENGTH(lsc.serviceCode > 0) OR LENGTH(lsc.masterLog > 0))

how to use length ?

Upvotes: 1

Views: 3143

Answers (2)

Piotr Szeptynski
Piotr Szeptynski

Reputation: 36

I came here because I had similar problem with using length in query builder. I tried ($qb->expr()->length('lsc.doorCode > 0') as suggested above but it did not work. The SQL output of this is "LENGTH(lsc.doorCode > 0)" which throws an error.

My solution to this problem is:

$qb->expr()->gt($qb->expr()->length('lsc.doorCode'), 0)

which generates "LENGTH(lsc.doorCode) > 0" that is correct SQL syntax.

Upvotes: 2

Taha Paksu
Taha Paksu

Reputation: 15616

You have a typo here:

$orX = $qb->expr()->orX();

    $orX
        ->add($qb->expr()->length('lsc.doorCode > 0')); <--
        ->add($qb->expr()->length('lsc.alarmCode > 0'))
        ->add($qb->expr()->length('lsc.serviceCode > 0'))
        ->add($qb->expr()->length('lsc.masterLog > 0'));

    $qb->andWhere($orX);

remove the ; and get rid of the syntax error.

And for the concat length, you can try something like this:

$query->andWhere("LENGTH( CONCAT_WS('', lsc.door_code, lsc.alarm_code, lsc.service_code, lsc.master_log) ) > 0")

After the update You can run native SQL queries with Doctrine like mentioned here:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html#the-nativequery-class

For reference: 

<?php
use Doctrine\ORM\Query\ResultSetMapping;

$rsm = new ResultSetMapping();
// build rsm here

$query = $entityManager->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', $rsm);
$query->setParameter(1, 'romanb');

$users = $query->getResult();

If your updated query works, this would be a way to make it work.

Upvotes: 1

Related Questions