How can I count the numbers of rows that a DQL query returned?

Question

I want to count number of rows whose return from DQL query,how i do this? here is my DQL query:

$items=$em->createQuery("select i.unitPrice,i.quantity,i.linetotal,i.description from InvoicesInvoicesBundle:Invoiceshasitems i where i.invoiceid='".$id."'");
$itemdata=$items->getResult();

do you want to show the serial number along the result? as 1,2,3.. or just the total number of records as 34 65 etc without any other data?? — Tauseef, Oct 08 '13 at 12:41
Use count http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html — Ankit Sharma, Oct 08 '13 at 12:42

Tauseef · Accepted Answer · 2013-10-08T13:03:14.143

1

Use Count()

$items=$em->createQuery("select COUNT(i.unitPrice)
                         from InvoicesInvoicesBundle:Invoiceshasitems i
                          where i.invoiceid='".$id."'");
    $itemdata=$items->getResult();

http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html

you should bookmark the above link if you are working regularly on DQL.

edited Oct 08 '13 at 13:03

answered Oct 08 '13 at 12:49

Tauseef

2,035
18
17

i used your code but this shows error like this [Syntax Error] line 0, col 24: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ',' – Muhammad Arif Oct 08 '13 at 12:58
try the updated query. may be ur db allows only 1 column in count, use the column that always have a value and i think unit price is the right one – Tauseef Oct 08 '13 at 13:00

Praveesh · Answer 2 · 2013-10-08T15:53:33.243

1

Try this code

 $qbTotal = $this->em->createQueryBuilder();
 $qbTotal->select('count(i)')
            ->from('InvoicesInvoicesBundle:Invoiceshasitems', 'i')
            ->where('i.invoiceid = :id')
            ->setParameter('id', $id)
             ;

 $qeuryTotal = $qbTotal->getQuery();
 $totalItems = $qeuryTotal->getSingleScalarResult();

edited Oct 08 '13 at 15:53

answered Oct 08 '13 at 13:15

Praveesh

1,257
1
10
23

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

Well, @Praveesh has the answer though there is a slight typo in it. Should be:id instead of ?id.

But this link has earned me 680 reps so I'm pretty sure it is correct: Count Rows in Doctrine QueryBuilder

edited May 23 '17 at 12:31

Community

1
1

answered Oct 08 '13 at 13:29

Cerad

48,157
8
90
92

hehe the lucky punch - others are working hard for their reps :-) 680 and counting ... but honestly i came across your answer and upvoted it months ago, too. greetings – Nicolai Fröhlich Oct 08 '13 at 21:26

How can I count the numbers of rows that a DQL query returned?

3 Answers3