Convert complex SQL to DQL or get working query in native (Doctrine, Mysql)

Hello I would like convert this query in DQL or in a pointagerepository , I have test in native query but return is [] an empty array

this is a query I would like convert

select m.mon, sum(p.hours)
from (select 'Jan' as mon, 1 as mm union all
      select 'Feb' as mon, 2 as mm union all
      select 'March' as mon, 3 as mm union all
      select 'Apr' as mon, 4 as mm union all
      select 'May' as mon, 5 as mm union all
      select 'June' as mon, 6 as mm union all
      select 'July' as mon, 7 as mm union all
      select 'Aug' as mon, 8 as mm union all
      select 'Sep' as mon, 9 as mm union all
      select 'Oct' as mon, 10 as mm union all
      select 'Nov' as mon, 11 as mm union all
      select 'Dec' as mon, 12 as mm
     ) m left join
     pointage p
     on p.date_point >= '2020/01/01' and '2021/01/01' and
        p.user_id = ? and
        month(p.date_point) = m.mm
group by m.mon
order by min(m.mm)

And user set dynamic as would the repository.

I haven’t any idea for convert it because is a subrequest and left join on my entity pointage

results like at :

//PointageRepository
public function findRecapTotalByUser($user)
    {
        return $this->createQueryBuilder('p')
            ->select('m.mon','SUM(p.hours)')
            ->from("(select 'Jan' as mon, 1 as mm union all
      select 'Feb' as mon, 2 as mm union all
      select 'March' as mon, 3 as mm union all
      select 'Apr' as mon, 4 as mm union all
      select 'May' as mon, 5 as mm union all
      select 'June' as mon, 6 as mm union all
      select 'July' as mon, 7 as mm union all
      select 'Aug' as mon, 8 as mm union all
      select 'Sep' as mon, 9 as mm union all
      select 'Oct' as mon, 10 as mm union all
      select 'Nov' as mon, 11 as mm union all
      select 'Dec' as mon, 12 as mm
     )", 'm')
            ->andWhere('p.user = :user') //with date point but is on the left join ...
            ->setParameter('user', $user)
            ->getQuery()
            ->getResult();
    }

Native query attempt

$rm = new ResultSetMapping();
        $rm->addEntityResult(Pointage::class, 'p')
            ->addFieldResult('p','hours', 'hours');
        $user = $this->getUser()->getId();
        $sql = $manager->createNativeQuery("
      SELECT m.mon, SUM(p.hours) FROM (
      SELECT 'Jan' as mon, 1 as mm union all
      SELECT 'Feb' as mon, 2 as mm union all
      SELECT 'March' as mon, 3 as mm union all
      SELECT 'Apr' as mon, 4 as mm union all
      SELECT 'May' as mon, 5 as mm union all
      SELECT 'June' as mon, 6 as mm union all
      SELECT 'July' as mon, 7 as mm union all
      SELECT 'Aug' as mon, 8 as mm union all
      SELECT 'Sep' as mon, 9 as mm union all
      SELECT 'Oct' as mon, 10 as mm union all
      SELECT 'Nov' as mon, 11 as mm union all
      SELECT 'Dec' as mon, 12 as mm
     ) m left join
     pointage p on p.date_point >= '2020-01-01' and '2021-01-01' and
        p.user_id = 1 and
        month(p.date_point) = m.mm
    group by m.mon
    order by min(m.mm)
 ", $rm)

And sub query is not allowed.

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *