for my API development I use apigility and doctrine. I'm currently programming an endpoint that users can like a post. Every post has an author. I have 2 tables with the following columns and example data inside:
Action
id post_id user_id createdAt
1 10 1 0000-00-00 00:00:00
2 12 2 0000-00-00 00:00:00
3 13 3 0000-00-00 00:00:00
(post_id = foreign key to post table) (user_id = source user which likes a post)
Post
id user_id createdAt
10 62 0000-00-00 00:00:00
12 4 0000-00-00 00:00:00
13 4 0000-00-00 00:00:00
(user_id = foreign key to user table)
What I need
I need the total_likes which an author (user_id) has. This can be queried over the Action table by joining the post table. I have already tried to create a DQL query for this:
// Get total likes an autor has received
$queryBuilder->select('count(p)')
->from('Db\Entity\Action', 'a')
->leftJoin('p.post', 'p')
->where('p.user = :user')
->setParameter('user', $targetUser);
$totalLikes = $queryBuilder->getQuery()->getSingleScalarResult();
Which this query, I get the wrong number of total_likes:
{
"total_likes": "2"
}
How do I have to correct this query that I get the correct count of likes of a user? With the example table data, the total_likes of user_id=62 should give me this back:
{
"total_likes": "1"
}