How can i implement this in Doctrine?
SELECT
(SELECT count(*)
FROM article
JOIN comments ON article.id = comments.article_id
WHERE
date(article.created) = '2015-02-06'
AND
article.id = 1) as a,
(SELECT count(*)
FROM article
JOIN comments ON article.id = comments.article_id
WHERE
date(article.created) = '2015-02-05'
AND
article.id = 1) as b
FROM article
GROUP BY article.id
LIMIT 1
This is what i am trying so far
public function createMyQuery($cdate, $aid){
$parameters = array(
'cdate' => $cdate,
'aid' => $aid,
);
$q = $this->getEntityManager()->createQueryBuilder()
->select(' ')
->addSelect('count(a.id)')
->from('ITJariSocialNetworkBundle:Article', 'a')
->where('article.created', ':cdate'))
->andWhere('article.id = :aid')
setParameters->($parameters)
;
return $q->getQuery();
}
//then i am looping on the calling of the function as following:
for($i=0; $i<7; $i++){
$postDate = date('Y-m-d',strtotime("-" .$i."days"));
$query = $this->createMyQuery(postDate, $aid);
}
$query->execute();
expecting to get the count of all the sub queries however i am getting Exception error result i thing the reason is i am doing the query in a wrong way