Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1858

LIKE and IS NULL operators not supported in HAVING clause

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.5
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Win7, Mysql

      Description

      The LIKE and IS NULL operators are not supported in HAVING clause.

      Work:
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu in (3,6)
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu = 3
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu >= 3
      ...

      Don't work:
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu LIKE 3
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NULL
      SELECT _a.id, count(_photos) as uuuu FROM Acme\CoreBundle\Entity\Member _a LEFT JOIN _a.photos _photos GROUP BY _a HAVING uuuu IS NOT NULL

        Activity

        Hide
        Nikolay Baklicharov added a comment -

        Guilherme Blanco Please backport to released version. This is very serious issue, especially when you make search queries.

        Show
        Nikolay Baklicharov added a comment - Guilherme Blanco Please backport to released version. This is very serious issue, especially when you make search queries.
        Hide
        Marco Pivetta added a comment -

        Nikolay Baklicharov this is an improvement, not a bug fix, therefore there will be no backporting.

        Show
        Marco Pivetta added a comment - Nikolay Baklicharov this is an improvement, not a bug fix, therefore there will be no backporting.
        Hide
        Nikolay Baklicharov added a comment -

        Marco Pivetta Then what workaround I can use? Lets say I do leftJoin on table and do AVG(object.value) > 3 OR object IS NULL. I.e. I want all results that have avg value higher than 3 and also results that do not have object at all?

        Show
        Nikolay Baklicharov added a comment - Marco Pivetta Then what workaround I can use? Lets say I do leftJoin on table and do AVG(object.value) > 3 OR object IS NULL. I.e. I want all results that have avg value higher than 3 and also results that do not have object at all?
        Hide
        Marco Pivetta added a comment -

        The current workaround is bumping the ORM version, which may or may not be a problem depending on your stability policies and how much you need this feature.

        Otherwise, NativeSQL until the functionality is available in a stable release.

        Show
        Marco Pivetta added a comment - The current workaround is bumping the ORM version, which may or may not be a problem depending on your stability policies and how much you need this feature. Otherwise, NativeSQL until the functionality is available in a stable release.
        Hide
        Nikolay Baklicharov added a comment - - edited

        Marco Pivetta Not an option for the project. However I found workaround that I will share here in case someone come here from google search like me.

        $queryBuilder->addSelect( 'COALESCE(AVG(ra.value), 0) AS HIDDEN averageRating' );
        $queryBuilder->leftJoin( 'c.ratings', 'ra' );
        $queryBuilder->andHaving( 'averageRating >= :avgRating' );
        $queryBuilder->orHaving( 'averageRating = 0' );
        $queryBuilder->setParameter( ':avgRating', $ratingFilter );
        Show
        Nikolay Baklicharov added a comment - - edited Marco Pivetta Not an option for the project. However I found workaround that I will share here in case someone come here from google search like me. $queryBuilder->addSelect( 'COALESCE(AVG(ra.value), 0) AS HIDDEN averageRating' ); $queryBuilder->leftJoin( 'c.ratings', 'ra' ); $queryBuilder->andHaving( 'averageRating >= :avgRating' ); $queryBuilder->orHaving( 'averageRating = 0' ); $queryBuilder->setParameter( ':avgRating', $ratingFilter );

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            PETIT Yoann
          • Votes:
            4 Vote for this issue
            Watchers:
            11 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: