Select id and its count in MySql/DQL (symfony query builder)

Question

I'm having trouble figuring out the logic behind this query.

Suppose we have a table

post_bump(post_id,user_id)

I want to result set to be - post_id, Number of times this post Id is in the table

I am using symfony's query builder which relies on DQL but if you know how to do it in SQL then I may be able to interpret the logic and put it into DQL myself.

Answer 1

In SQL, you can do this using a group by and count:

select post_id,
    count(*) as times
from post_bump
group by post_id;

I am not an expert in Symfony, but see if this helps:

• SQL query with count and group by in Symfony2 QueryBuilder

Answer 2

For example if you have the following table:

SELECT post_bump.post_id, Count(post_bump.post_id) AS NumberOfPost_id FROM post_bump GROUP BY post_bump.post_id;