在Entity Symfony2中创建自定义搜索

我是Symfony的新手，但我正在努力开发一个适合我工作的小型系统。

我只有ZF1的经验，我不习惯使用条令。但我需要为我的页面创建一个自定义查询，在互联网上搜索时，我发现当我生成一个新实体时，我可以创建一个在类名中具有相同名称+存储库的新实体。

例如，我有一个名为Schedule的实体和一个名为主ScheduleRepository。

在我尝试生成自定义查询的ScheduleRepository中。

class ScheduleRepository extends EntityRepository
{
   public function findAll()
   {
      $rsm = new ResultSetMapping();
      # ScheduleType
      $rsm->addEntityResult( "ApplicationBundle'Entity'Schedule", "s" );
      $rsm->addFieldResult( "s", "schedule_id", "id" );
      $rsm->addFieldResult( "s", "usersId", "usersId" );
      $rsm->addFieldResult( "s", "updatedAt", "updatedAt" );
      $rsm->addFieldResult( "s", "start", "createdAt" );
      $rsm->addFieldResult( "s", "finish", "createdAt" );
      $rsm->addFieldResult( "s", "schedulesTypeId", "schedulesTypeId" );      
      # User
      $rsm->addJoinedEntityResult( "ApplicationBundle'Entity'User", "u", "s", "users" );
      $rsm->addFieldResult( "u", "users_id", "id" );
      $rsm->addFieldResult( "u", "name", "name" );
      $rsm->addJoinedEntityResult( "ApplicationBundle'Entity'ScheduleType", "st", "s", "scheduleType" );
      $rsm->addFieldResult( "st", "schedule_type_id", "id" );
      $rsm->addFieldResult( "st", "schedule_type_name", "name" );
      # custom SQL
      $sql = "SELECT
               s.id AS schedule_id,
               s.updated_at,
               st.id AS schedule_type_id,
               st.name AS schedule_type_name,
               u.id AS users_id,
               u.name,
               MAX( CASE WHEN s.schedules_type_id = 2 THEN s.created_at END ) AS start,
               MAX( CASE WHEN s.schedules_type_id = 2 THEN s.created_at END ) AS finish
             FROM
               schedules s
             INNER JOIN users u ON u.id = s.users_id
             INNER JOIN schedules_type st ON st.id = s.schedules_type_id
             GROUP BY 
               s.users_id,
               DATE_FORMAT( s.created_at, '%Y-%m-%d' ) 
             ORDER BY 
               DATE_FORMAT( s.created_at, '%Y-%m-%d' ) ASC";
      return $this->_em->createNativeQuery( $sql, $rsm )->getResult();
   }

当我转储退货时，我收到的是：

Schedule {#2187
  -id: 1
  -users: User {#2189
    -id: 1
    -name: "Felipe"
    -email: null
    -password: null
    -active: null
    -createdAt: null
    -updatedAt: null
    #schedule: PersistentCollection {#2190
      -snapshot: []
      -owner: User {#2189}
      -association: array:15 [ …15]
      -em: EntityManager {#2092 …10}
      -backRefFieldName: "users"
      -typeClass: ClassMetadata {#2101 …}
      -isDirty: false
      -initialized: false
      -coll: ArrayCollection {#2191
        -_elements: []
      }
    }
  }
  -scheduleType: ScheduleType {#2180
    -id: 1
    -name: "Entrada"
    #schedule: PersistentCollection {#2181
      -snapshot: []
      -owner: ScheduleType {#2180}
      -association: array:15 [ …15]
      -em: EntityManager {#2092 …10}
      -backRefFieldName: "scheduleType"
      -typeClass: ClassMetadata {#2101 …}
      -isDirty: false
      -initialized: false
      -coll: ArrayCollection {#2182
        -_elements: []
      }
    }
  }
  -createdAt: DateTime {#2188
    +"date": "2015-02-18 19:31:39"
    +"timezone_type": 3
    +"timezone": "UTC"
  }
  -updatedAt: null
}

我的问题是：

• 为什么我没有收到我在查询中创建的开始和结束别名
• 如果我在该字段中有值，为什么我的updatedAt为空

谢谢。

更新

我正在尝试用户[user3749178]：(https://stackoverflow.com/users/3749178/user3749178)说，使用这个库github.com/mapado/MysqlDoctrineFunctions，我再次生成查询，但在DQL中，现在我收到一个错误：

[Syntax Error] line 0, col 73: Error: Unexpected ')'

在这个DQL:

SELECT u, s, st, MAX(CASE WHEN s.schedulesTypeId = 2 THEN s.createdAt END) AS start FROM ApplicationBundle:Schedule s JOIN s.users u JOIN s.scheduleType st