Symfony 2 & Doctrine: select count and group by return false results

Question

I have a table like this

ID | ADKEY | IP | CREATED_AT 1 | NS0392DJEJ | 127.0.0.1 | 2016-04-25 09:00:00 2 | NS0392DJEJ | 127.0.0.1 | 2016-04-25 09:20:00 3 | UI0392DJPO | 127.0.0.1 | 2016-04-25 09:30:00

The goal is to add a new row each time someone click on the advertise on the website.

My problem is when i want to get the number of click by advertise (the name of the advertise is the adkey column)

In my example, i should get NS0392DJEJ ==> 2 UI0392DJPO ==> 1

But with the following request, the count is always 1

First, the global getQueryBuilder()

 */ public function getQueryBuilder() { $queryBuilder = $this->getEntityManager()->createQueryBuilder(); $queryBuilder ->select('e') ->from($this->getEntityClass(), 'e') ->orderBy('e.id', 'DESC'); return $queryBuilder; }

Then, i extend it in my child class

public function getQueryBuilder() { $qb = parent::getQueryBuilder(); $qb->addSelect( $qb->expr()->count('e.id') . 'as adCount'); $qb->addGroupBy('e.adkey'); }

When I analyze the request i notice that Doctrine made a first request to get the id and then use these id to do the count request

SELECT DISTINCT id0 FROM ( SELECT a0_.id AS id0, a0_.adkey AS adkey1, a0_.ip AS ip2, a0_.created_at AS created_at3, COUNT(a0_.id) AS sclr4 FROM advertiser_clickcounter a0_ GROUP BY a0_.adkey ORDER BY a0_.id DESC ) dctrn_result LIMIT 30 OFFSET 0

And then

SELECT a0_.id AS id0, a0_.adkey AS adkey1, a0_.ip AS ip2, a0_.created_at AS created_at3, COUNT(a0_.id) AS sclr4 FROM advertiser_clickcounter a0_ WHERE a0_.id IN (?) GROUP BY a0_.adkey ORDER BY a0_.id DESC Parameters: [['5', '4', '2']]

Do you have some advice ? what would be the correct way to do that ? Knowing the fact that I need a QueryBuilder Instance as a result (or if you know a way to convert a Query to QueryBuilder Instance ? )

Thank you

Richard · Accepted Answer · 2016-04-25 20:17:33Z

What you're doing there doesn't look wrong in itself. It's a very simple group by query though, there's no real need to extend any classes. May as well do it in one hit.

This will work just fine (I made some assumptions about what your entities might be called obviously):

 $yourCount = $qb->select('count(a.adkey) as adKeyCount, a.adKey') ->from('YourBundle:YourAdKeyEntity', 'a') ->groupBy('a.adKey') ->getQuery() ->getResult();

There's no point selecting the AdKey entity as you will only get one of the grouped entities anyway.

Unfortunately i can't do that, i didn't create this code and i just need to use it. Plus, i need to return a QueryBuilder Instance (because it's used after by other libs), so i can't use getQuery()->getResult()

Collectives™ on Stack Overflow

Symfony 2 & Doctrine: select count and group by return false results

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related