Use SQL functions in Doctrine native queries

Question

I want to use the SUM function in a Doctrine native query but I keep getting empty results. Here is my code:

    $em = $this->getDoctrine()->getManager();

    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('AndreiStatisticsBundle:Visit', 'v');
    $rsm->addScalarResult('counter', 'counter');
    $rsm->addScalarResult('created_at', 'created_at');

    $query = $em->createNativeQuery(
        'SELECT SUM(counter) FROM visits GROUP BY created_at', 
        $rsm
    );

Is interesting because if I don't use the SUM function the code will work:

    $em = $this->getDoctrine()->getManager();

    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('AndreiStatisticsBundle:Visit', 'v');
    $rsm->addScalarResult('counter', 'counter');
    $rsm->addScalarResult('created_at', 'created_at');

    $query = $em->createNativeQuery(
        'SELECT counter FROM visits GROUP BY created_at', 
        $rsm
    );

Can someone figures out what am I missing?

Pi Wi · Accepted Answer · 2013-11-14 10:43:15Z

1

Can you try this?

$em = $this->getDoctrine()->getManager();

$repository = $em->getRepository('AndreiStatisticsBundle:Visit');
$qb = $repository->createQueryBuilder('v');
$qb->select('SUM(v.counter) AS counterSum');
$qb->groupBy('v.created_at');

$count = $qb->getQuery()->getSingleScalarResult();

edited Nov 14, 2013 at 10:43

answered Nov 14, 2013 at 10:04

Pi Wi

1,0851 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

zuzuleinen Over a year ago

I get an [Semantical Error] line 0, col 31 near 'FROM Andrei\StatisticsBundle\Entity\Visit': Error: Class 'FROM' is not defined.

Pi Wi Over a year ago

Can you post the output of $qb->getQuery()->getDQL(); ?

zuzuleinen Over a year ago

"SELECT SUM(v.counter) AS count FROM Andrei\StatisticsBundle\Entity\Visit v GROUP BY v.created_at"

Pi Wi Over a year ago

Ah, AS count is wrong, we can't use keywords in the DQL. I edited my answer

zuzuleinen Over a year ago

Can you edit your response to select multiple values, let's say select also created_at?

|

Collectives™ on Stack Overflow

Use SQL functions in Doctrine native queries

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related