DQL complicated Query

Question

Ok I have 2 tables, the relevant structure is the following:

**youtubevideo**
id - int
allViews - relational


**views**
id - int
youtubevideo_id - int
viewCount - int
firstFetch - date

Its relevant that youtubevideo has many views. This is presented by a OneToMany in a Symfony entity.

Now, I have 2 dates, lets call them fromDate and toDate. I need to select all youtubevideos and order them by the viewCount sum of the views that belong to them.

And I only want to use the views that have their firstFetch date, between the fromDate and the toDate.

So I imagine it would be something like

SELECT y FROM YTScraperBundle:YouTubeVideo y 
JOIN y.allViews v GROUP BY y.id 
ORDER BY SUM(v.viewCount) LIMIT 0, 30; <-- or does this need to be an inline select to the specific element?

And I don't really see how to put WHERE v.firstFetch is between fromDate and toDate in there.

UPDATE

$query = $this->em->createQuery(
    "SELECT y, IF(v IS NULL, 0, SUM(v.viewCount)) AS HIDDEN sumviewcount
    FROM YTScraperBundle:YouTubeVideo y
    LEFT JOIN y.allViews v WITH v.firstFetch BETWEEN :fromDate AND :toDate
    GROUP BY y ORDER BY sumviewcount DESC
    "
);
$query->setMaxResults(self::PR_PAGE);
$query->setFirstResult($firstResult);
$query->setParameter('fromDate', $fromDate);
$query->setParameter('toDate', $toDate);

Getting the error:

Expected known function, got 'IF'

Answer 1

You have to use WITH, BETWEEN, and bind two DateTime objects into a parameterized query:

$result = $this->getDoctrine()->getManager()->createQuery('
    SELECT y, 
        CASE WHEN (v IS NULL) THEN 0 ELSE SUM(v.viewCount) END AS HIDDEN sumviewcount
    FROM YTScraperBundle:YouTubeVideo y 
    LEFT JOIN y.allViews v WITH v.firstFetch BETWEEN :fromDate AND :toDate
    GROUP BY y ORDER BY sumviewcount DESC
')->setParameter('fromDate', new \DateTime('12-34-5678 00:00:00')) // Replace this with an ISO date
->setParameter('toDate', new \DateTime('12-34-5678 00:00:00')) // Replace here too
->getResult();

I've combined @Matteo's answer concerning the SUM into mine for posterity. Credit goes to him for the HIDDEN idea.