无效的路径表达必须是StateFieldPath表达

Question

我目前正在使用Symfony3.4在一个项目中工作，一个项目是关于预订酒店的室。
我有两个实体room(id,...)和bookings(room,check_in,check_out,...)，它们之间的关系是OneToMany。
我正在尝试检索check_in和check_out字段未预订的房间。
我在RoomRepository中做了一个名为findRoomsNotBookedByCheckInCheckOut

的功能

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();
        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b.room FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");
        die($q->getSQL());
        return $q->getResult();
    }  

当我从控制器调用此功能

时

private function roomsByCheckInCheckOut(Request $request, $em) {
        if ($request->query->get('check-in') != null && $request->query->get('check-out') != null) {
           $rooms = $em->getRepository('AppBundle:Room')->findRoomsNotBookedByCheckInCheckOut($request);
        }
        return null;
    }  

我有一个错误，即：

[Semantical Error] line 0, col 57 near 'room FROM AppBundle:Bookings': Error: Invalid PathExpression. Must be a StateFieldPathExpression.  

我尝试通过将b.room更改为b.room.id来解决：

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();
        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b.room.id FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");
        die($q->getSQL());
        return $q->getResult();
    }

我得到了另一个错误，即：

[Semantical Error] line 0, col 62 near 'id FROM AppBundle:Bookings': Error: Class AppBundleEntityBookings has no field or association named room.id

这是逻辑的，因为实体bookings没有room.id，但它具有room。
我再次尝试通过将b.room更改为b来解决，并将功能变为：

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();
        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");
        die($q->getSQL());
        return $q->getResult();
    }

它工作正常，没有任何错误，但没有检索正确的结果。
注意：我在MySQL数据库中测试了SQL查询，并获得了正确的结果

Answer 1

我只是通过添加IDENTITY(b.room)解决了我的问题。

$q = $em->createQuery("SELECT r.id FROM AppBundle:Room r "
        . "WHERE r.id NOT IN ( "
        . "SELECT IDENTITY(b.room) FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut < :checkIn "
        . "OR b.checkIn > :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', $chIn)
        ->setParameter('checkOut', $chOut);