Why is Doctrine creating a Where Clause as part of the Query Builder when one isn't included?-CodePudding

Why is Doctrine creating a WHERE Clause??

The SQL generates the correct number of assets, but when using DQL I get a limited number of records. I tried to use NativeSQL and map the results but that didn't work.

Below is the code from the repository where the querybuilder function is:

public function getWithSearchQueryBuilderSchedule(?string $term): QueryBuilder
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb
    ->select('DISTINCT b.name as building, ac.name as category,
      SUM(CASE WHEN Month(jh.duedate) = 1 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as january,
      SUM(CASE WHEN Month(jh.duedate) = 2 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as february,
      SUM(CASE WHEN Month(jh.duedate) = 3 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as march,
      SUM(CASE WHEN Month(jh.duedate) = 4 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as april,
      SUM(CASE WHEN Month(jh.duedate) = 5 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as may,
      SUM(CASE WHEN Month(jh.duedate) = 6 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as june,
      SUM(CASE WHEN Month(jh.duedate) = 7 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as july,
      SUM(CASE WHEN Month(jh.duedate) = 8 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as august,
      SUM(CASE WHEN Month(jh.duedate) = 9 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as september,
      SUM(CASE WHEN Month(jh.duedate) = 10 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as october,
      SUM(CASE WHEN Month(jh.duedate) = 11 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as november,
      SUM(CASE WHEN Month(jh.duedate) = 12 AND YEAR(jh.duedate) = 2021 THEN 1 ELSE 0 END) as december'       
      )
    ->from('App\Entity\Asset', 'a')
    ->leftJoin('a.jobHistories', 'jh')
    ->leftJoin('a.assetlocation', 'al')
    ->leftJoin('al.building', 'b')
    ->leftJoin('b.buildingSignOffs', 'bso')
    ->leftJoin('b.departments', 'd')
    ->leftJoin('b.riskAssessments', 'ris')
    ->leftJoin('a.assetsubcategory', 'asubc')
    ->leftJoin('asubc.assetcategory', 'ac')
    ->groupBy('building, category')
    ->orderBy('b.name', 'ASC');

    return $qb;

}

Below is the Code from the Controller:

public function show(BuildingRepository $buildingRepository, Request $request, PaginatorInterface $paginator): Response
{
    $assetlocation = new AssetLocation();

    $form = $this->createForm(AssetLocationType::class, $assetlocation);
    $q = $request->query->get('q');

    $queryBuilder = $buildingRepository->getWithSearchQueryBuilderSchedule($q);
    $queryBuilder->resetDQLPart('where');
    $pagination = $paginator->paginate(
        $queryBuilder, /* query NOT result */
         $request->query->getInt('page', 1)/*page number*/,
         50000/*limit per page*/
    );

    

    return $this->render('schedule/index.html.twig', [
        'form' => $form->createView(),
        'pagination' => $pagination,

    ]);
}

And this is the code from the Symfony Profiler Doctrine ouput:

SELECT DISTINCT b0_.name                                                                              AS name_0,
                a1_.name                                                                              AS name_1,
                SUM(CASE WHEN MONTH(j2_.duedate) = 1 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_2,
                SUM(CASE WHEN MONTH(j2_.duedate) = 2 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_3,
                SUM(CASE WHEN MONTH(j2_.duedate) = 3 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_4,
                SUM(CASE WHEN MONTH(j2_.duedate) = 4 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_5,
                SUM(CASE WHEN MONTH(j2_.duedate) = 5 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_6,
                SUM(CASE WHEN MONTH(j2_.duedate) = 6 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_7,
                SUM(CASE WHEN MONTH(j2_.duedate) = 7 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_8,
                SUM(CASE WHEN MONTH(j2_.duedate) = 8 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_9,
                SUM(CASE WHEN MONTH(j2_.duedate) = 9 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END)  AS sclr_10,
                SUM(CASE WHEN MONTH(j2_.duedate) = 10 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END) AS sclr_11,
                SUM(CASE WHEN MONTH(j2_.duedate) = 11 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END) AS sclr_12,
                SUM(CASE WHEN MONTH(j2_.duedate) = 12 AND YEAR(j2_.duedate) = 2021 THEN 1 ELSE 0 END) AS sclr_13
FROM asset a3_
         LEFT JOIN job_history j2_ ON a3_.id = j2_.asset_id
         LEFT JOIN asset_location a4_ ON a3_.assetlocation_id = a4_.id
         LEFT JOIN building b0_ ON a4_.building_id = b0_.id
         LEFT JOIN building_sign_off b5_ ON b0_.id = b5_.building_id
         LEFT JOIN department d6_ ON b0_.id = d6_.building_id
         LEFT JOIN risk_assessment r7_ ON b0_.id = r7_.building_id
         LEFT JOIN asset_sub_category a8_ ON a3_.assetsubcategory_id = a8_.id
         LEFT JOIN asset_category a1_ ON a8_.assetcategory_id = a1_.id
WHERE a3_.id IN
      (3501, 3502, 3504, 3506, 3454, 3452, 3606, 3607, 3609, 3610, 3457, 3455, 3436, 3434, 3525, 3526, 3528, 3530, 3507,
       3508, 3510, 3511, 3531, 3532, 3535, 3537, 3439, 3440, 3437, 3460, 3461, 3458, 3464, 3465, 3462, 3468, 3466, 3471,
       3469, 3443, 3444, 3441, 3538, 3539, 3541, 3447, 3448, 3445, 3512, 3513, 3514, 3518, 3413, 3411, 3410, 3519, 3520,
       3522, 3524, 3543, 3542, 3544, 3545, 3550, 3451, 3449, 3551, 3552, 3553, 3556, 3558, 3559, 3561, 3563, 3564, 3565,
       3567, 3569, 3570, 3571, 3573, 3575, 3474, 3472, 3477, 3478, 3475, 3481, 3479, 3422, 3418, 3416, 3414, 3417, 3576,
       3577, 3578, 3582, 3583, 3585, 3586, 3588, 3595, 3596, 3598, 3599, 3426, 3424, 3427, 3498, 3499, 3496, 3430, 3428,
       3600, 3601, 3602, 3605, 3433, 3431, 3611, 3612, 3613, 3617, 2897, 2899, 2898, 2908, 3484, 3482, 1780, 1783, 1784,
       1788, 1802, 1711, 1, 3, 5, 3282, 3279, 2800, 2804, 1712, 1673, 20, 1740, 21, 44, 2852, 2837, 2858, 2844, 2850,
       2743, 2719, 3618, 3620, 3621, 3625, 2855, 2875, 2876, 2877, 3274, 2664, 2665, 1714, 1675, 136, 1744, 135, 140,
       2633, 2638, 2702, 2704, 2674, 2685, 2709, 2710, 2712, 2714, 2711, 2713, 2789, 2790, 2799, 2794, 1718, 1680, 254,
       1748, 256, 263, 3485, 1720, 1681, 367, 1755, 368, 376, 3488, 3486, 3297, 3300, 3397, 3306, 3311, 1723, 1690,
       1936, 1759, 1937, 1954, 1943, 3257, 3113, 3240, 3127, 2659, 2660, 3491, 3489, 3492, 1728, 1700, 682, 1761, 683,
       701, 2806, 2811, 2812, 2821, 2945, 2950, 2935, 2955, 2979, 2966, 3001, 2952, 3025, 3287, 1730, 1705, 2236, 1764,
       2237, 1778, 2241, 1733, 1706, 1101, 1770, 1100, 1105, 2864, 2866, 2863, 3277, 3276, 2823, 2827, 2825, 2785, 3639,
       3652, 3653, 3656, 2654, 2655, 3291, 3293, 3290, 2648, 2649, 1736, 1707, 1382, 1773, 1383, 1386, 3266, 3264, 1716,
       1781, 1677, 1809, 1747, 1808, 1815, 3495, 3493, 2783, 2767, 2765, 2745, 3684, 2874, 2717, 2715, 3294, 1738, 1709,
       1515, 1514, 1533, 2861)
GROUP BY b0_.name, a1_.name
ORDER BY b0_.name ASC;

There shouldn't be a Where clause at all, and using resetDQLPart('where') isn't removing it either, and the asset ids are in building name order, and quite a few asset ids are missing, so how has it chosen these asset ids.

CodePudding user response：

Doctrine is trying to be smart.

Instead of running a single, more complex and heavier query, it splits the query in two parts.

First gets the IDs, and then feeds the IDs to the WHERE id IN() clause you see on the profiler.

If you check the profiler, you'll see another query before this one, which is the one that gets the IDs it uses to filter this one.

CodePudding user response：

SOLVED:

I removed the DQL, adding the NativeSQL to the controller and linked in to the pagination that way, code below is the extract from the Controller

    $conn = $this->entityManager->getConnection();
    $sql = "SELECT DISTINCT `building`.`name` as building, `asset_category`.`name` as category, 
    SUM(CASE WHEN Month(job_history.duedate) = 1 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as january,
    SUM(CASE WHEN Month(job_history.duedate) = 2 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as february,
    SUM(CASE WHEN Month(job_history.duedate) = 3 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as march,
    SUM(CASE WHEN Month(job_history.duedate) = 4 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as april,
    SUM(CASE WHEN Month(job_history.duedate) = 5 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as may,
    SUM(CASE WHEN Month(job_history.duedate) = 6 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as june,
    SUM(CASE WHEN Month(job_history.duedate) = 7 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as july,
    SUM(CASE WHEN Month(job_history.duedate) = 8 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as august,
    SUM(CASE WHEN Month(job_history.duedate) = 9 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as september,
    SUM(CASE WHEN Month(job_history.duedate) = 10 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as october,
    SUM(CASE WHEN Month(job_history.duedate) = 11 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as november,
    SUM(CASE WHEN Month(job_history.duedate) = 12 AND YEAR(job_history.duedate) = 2021 THEN 1 ELSE 0 END) as december
    FROM `asset`
        LEFT JOIN `job_history` ON `job_history`.`asset_id` = `asset`.`id`
        LEFT JOIN `asset_location` ON `asset`.`assetlocation_id` = `asset_location`.`id`
        LEFT JOIN `building` ON `asset_location`.`building_id` = `building`.`id`
        LEFT JOIN `building_sign_off` ON `building_sign_off`.`building_id` = `building`.`id`
        LEFT JOIN `department` ON `asset_location`.`department_id` = `department`.`id`
        LEFT JOIN `risk_assessment` ON `risk_assessment`.`building_id` = `building`.`id`
        LEFT JOIN `asset_sub_category` ON `asset`.`assetsubcategory_id` = `asset_sub_category`.`id`
        LEFT JOIN `asset_category` ON `asset_sub_category`.`assetcategory_id` = `asset_category`.`id`
        WHERE job_history.id > 0
        GROUP BY building, category
    ORDER BY building.name";
    $statement = $conn->prepare($sql);
    $statement->execute();
    $data = $statement->fetchall();


$result = $paginator->paginate(
    $data,
    $request->query->getInt('page',1),
    $request->query->getInt('limit', 10)
);