php - Doctrine DQL join tables -

for api development use apigility , doctrine. i'm programming endpoint users can post. every post has author. have 2 tables following columns , 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 post table) (user_id = source user likes 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 user table)

what need

i need total_likes author (user_id) has. can queried on action table joining post table. have tried create dql query this:

// total likes 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 query, wrong number of total_likes:

{   "total_likes": "2" } 

how have correct query correct count of likes of user? example table data, total_likes of user_id=62 should give me back:

{   "total_likes": "1" } 

try this:

// total likes autor has received $querybuilder     ->select('count(p)')     ->from('db\entity\action', 'a')     ->leftjoin(         'db\entity\post',         'p',         \doctrine\orm\query\expr\join::with,         'a.post = p.id'      )      ->where('p.user = :user')      ->setparameter('user', $users);      $totallikes = $querybuilder->getquery()->getsinglescalarresult(); }