CODEWITHSUNDEEP
symfonydoctrine
Kamil P
Kamil P

Reputation: 784

symfony if function in repository

I'm trying to execute this function in repository

public function getInfoAsManagers()
{
    $q = $this->createQueryBuilder('user');
    $q->select(['user.id', 'user.firstName', 'user.lastName', 'position.name', 'IF(COUNT(mpkManager.user)>0, 1, 0) as isManager']);
    $q->leftJoin('user.position', 'position', 'WITH', 'user.position=position');
    $q->leftJoin('PCK\ExtraBundle\Entity\MpkManager', 'mpkManager', 'WITH', 'mpkManager.user=user');
    $q->groupBy('user');

    return $q->getQuery()->getResult();
}

but i've got on screen

[Syntax Error] line 0, col 62: Error: Expected known function, got 'IF'

How can I use IF function in my custom query?

Can I use something like this?

$q->addSelect(DB::raw('PURE SQL'));

Upvotes: 0

Views: 428

Answers (1)

rommct
rommct

Reputation: 228

Just add createNativeQuery()

OR CASE 

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder();
$q = $qb
  ->select("c.id")
  ->addSelect("CASE WHEN (c.parent IS NULL) THEN c.name ELSE 'something' END")
  ->from("MyBundle:Category", "c")
  ->leftJoin("c.parent", "t");

echo $q->getQuery()->getSQL();

ELSE ADD IF FUNCTION

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language

Upvotes: 2

Related Questions