Geek Answers Handbook

DQL complex query

Ok I have 2 tables, the corresponding structure is as follows:

**youtubevideo**
id - int
allViews - relational


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

Accordingly, youtubevideo has many views. This is represented by OneToMany in a Symfony object.

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

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

So, I think it will 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 really don't see how to set WHERE v.firstFetch between them and fromDate.

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 error:

Expected known function, got 'IF'
+4
source share
2 answers

WITH, BETWEEN DateTime :

$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();

@Matteo SUM . HIDDEN.

+3

HIDDEN Doctrine2 ( Doctrine 2.2 +).

SELECT y, 
       SUM(v.viewCount) AS HIDDEN sumviewcount 
FROM YTScraperBundle:YouTubeVideo y 
JOIN y.allViews v  
GROUP BY y
ORDER BY sumviewcount 
LIMIT 0, 30;

.

+3

More articles:


All Articles