Doctrine 1
  1. Doctrine 1
  2. DC-964

Alias name error when using interval(MySQL)

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      PHP Version 5.3.3-1ubuntu9.3; Apache/2.2.16 (Ubuntu); MySQL: 5.1.41; Symfony 1.4; Doctrine 1.2.3 (Revision: 7490)

      Description

      I'm using Doctrine with Symfony and i have some troubles when i'm using the mysql command: interval. Here's a detailed explanation:
      Part of the schema:

      User:
        columns:
          name: { type: string(255), notnull: true }
          nick: { type: string(255), notnull: true, unique: true }
          email: { type: string(255), notnull: true, unique: true }
          password: { type: string(40), notnull: true }
      
      VirtualConferenceRoom:
        columns:
          adminId: { type: integer, notnull: true }
          name: { type: string(255), notnull: true }
          startDate: { type: timestamp, notnull: true }
          duration: { type: integer, notnull: true }
        relations:
          Admin:
            class: User
            foreign: id
            local: adminId
      
      VirtualConferenceRoomUser:
        columns:
          virtualConferenceRoomId: { type: integer, notnull: true }
          userId: { type: integer, notnull: true }
        relations:
          VirtualConferenceRoom:
            class: VirtualConferenceRoom
            foreign: id
            local: virtualConferenceRoomId
          User:
            class: User
            foreign: id
            local: userId
      

      I've generated my model with symfony's built in command(symfony doctrine:build --model --sql)
      When I try to get the conference rooms which are in progress at the moment I try it like this:

      // In the project/lib/model/doctrine/VirtualConferenceRoomTable.class.php which is extends from Doctrine_Table
      public function getRoomsInProgressByUserId($userId)
      {
          return Doctrine_Query::create()
                      ->select('vcr.*')
                      ->from('VirtualConferenceRoom vcr')
                      ->innerJoin('vcr.VirtualConferenceRoomUser vcru')
                      ->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId))
                      ->execute();
      }
      

      When I try to run my app I get an error message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vcr.Duration' in 'where clause'"
      The generated sql command is:

      " SELECT v.id AS v__id, v.userid AS v__userid, v.name AS v__name, v.startdate AS v__startdate, v.duration AS v__duration FROM virtual_conference_room v INNER JOIN virtual_conference_room_user v2 ON v.id = v2.virtualconferenceroomid WHERE (v.startdate < NOW() AND v.startdate + interval vcr.Duration minute > NOW() AND v2.userid = ?) - (2)"

      The problem is the vcr.Duration section. If I change my function from:

      ->where('(vcr.StartDate < now()) and (vcr.StartDate + interval vcr.Duration minute > now()) and vcru.UserId = ?', array($userId))
      

      to:

      ->where('(vcr.StartDate < now()) and (vcr.StartDate + interval v.Duration minute > now()) and vcru.UserId = ?', array($userId))
      

      it works perfectly.

      I hope my report is useful and clear and i can help you to improve this great ORM
      Best wishes: Tibor Erdész <erdeszt@gmail.com>

        Activity

        Hide
        Tibor Erdész added a comment -

        Just figured out that I can use

        
        

        notations and I fixed my report.

        Show
        Tibor Erdész added a comment - Just figured out that I can use notations and I fixed my report.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Tibor Erdész
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: