Блог

Я пытаюсь преобразовать sql-запрос в querybuilder.

 SELECT c.id, SUM(a.view_count), dt
FROM project_content c
JOIN project_content_analytic a ON a.content_id = c.id
RIGHT JOIN (SELECT c.id, DATE_FORMAT(a.created_at, '%d-%m-%Y') dt, MAX(a.created_at) max_date
            FROM project_content c
            JOIN project_content_analytic a ON a.content_id = c.id
            GROUP BY dt, c.id) x 
ON x.max_date = a.created_at and x.id = c.id
GROUP BY dt  
ORDER BY `x`.`dt` ASC
  

Я пытался:

         $qb->select('c.id')
            ->addSelect('COALESCE(SUM(a.viewCount),0) as viewCount')
            ->addSelect('DATE_FORMAT(a.createdAt, '%d-%m-%Y') as date')
            ->innerJoin('c.analytics', 'a')
            ->groupBy('c.id')
            ->addGroupBy('date')
            ->orderBy('a.createdAt', 'DESC');
  

Подзапрос:

         $sub->select('cc.id')
            ->addSelect('MAX(aa.created_at) as maxDate')
            ->addSelect('DATE_FORMAT(aa.createdAt, '%d-%m-%Y') as ddate')
        $sub->innerJoin('cc.analytics', 'aa')
            ->addGroupBy('ddate')
            ->orderBy('aa.createdAt', 'DESC');
  

Но я не знаю, как объединить запросы.

Спасибо за вашу помощь.