Doctrine 1
  1. Doctrine 1
  2. DC-40

Problems with alias in where section

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Invalid
    • Affects Version/s: 1.2.0-ALPHA1
    • Fix Version/s: 1.2.0-ALPHA2
    • Component/s: Query
    • Labels:
      None
    • Environment:
      MAMP

      Description

      I'm using a mysql function to calculate the distance between 2 points. When I'm using this funciton in a doctrine query it works. But when I'm using the functions result in a where clause it dont work:

      The Query:
      $basicQuery = $query->select('id, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, IF(count(r.id) > 0, AVG(r.rating), 0) as ratingAvg')
      ->from('ProomsOffer o')
      ->leftJoin('o.PublicTransports pt')
      ->leftJoin('o.Unavailabilities ua')
      ->leftJoin('o.Ratings r')
      ->leftJoin('o.Bookings b')
      ->where('distance != 0')
      ->groupBy('o.id')
      ->orderBy('distance, ratingAvg');

      The Error message:
      SQLSTATE[42S22]: Column not found: 1054 Unknown column 'm__1' in 'where clause'
      #0 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql))
      #1 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(790): Doctrine_Connection->execute('SELECT COUNT...', Array)
      #2 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Query.php(2037): Doctrine_Connection->fetchAll('SELECT COUNT...', Array)
      #3 /Applications/MAMP/htdocs/prooms/cms/application/modules/prooms/controllers/SearchController.php(163): Doctrine_Query->count()
      #4 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Action.php(513): Prooms_SearchController->searchAction()
      #5 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('searchAction')
      #6 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(CMS_Controller_Request), Object(Zend_Controller_Response_Http))
      #7 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(372): Zend_Controller_Front->dispatch()
      #8 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(241): CMS_Application->_dispatch(Object(Zend_Controller_Front))
      #9 /Applications/MAMP/htdocs/prooms/cms/public/index.php(50): CMS_Application->run()
      #10

      {main}

      The getSqlQuery:
      SELECT `m`.`id` AS `m_id`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m0`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m1`, IF(COUNT(`m4`.`id`) > 0, AVG(`m4`.`rating`), 0) AS `m41` FROM `module_prooms_offers` `m` LEFT JOIN `module_prooms_offers_public_transport` `m2` ON `m`.`id` = `m2`.`id_offer` LEFT JOIN `module_prooms_offers_unavailability` `m3` ON `m`.`id` = `m3`.`id_offer` LEFT JOIN `module_prooms_offers_ratings` `m4` ON `m`.`id` = `m4`.`id_offer` LEFT JOIN `module_prooms_bookings` `m5` ON `m`.`id` = `m5`.`id_offer` WHERE `m1` != 0 GROUP BY `m`.`id` ORDER BY `m1`, `m4_1`

        Activity

        Hide
        Guilherme Blanco added a comment -

        You must disable PORTABILITY_EXPR in order to get it working correcrly.

        Show
        Guilherme Blanco added a comment - You must disable PORTABILITY_EXPR in order to get it working correcrly.

          People

          • Assignee:
            Roman S. Borschel
            Reporter:
            Johannes Weber
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: