Invalid PathExpression must be a StateFieldPathExpression

Question

I am currently working in a project using Symfony3.4, a project is about booking rooms of an hotel.
I have two entities room(id,...) and bookings(room,check_in,check_out,...) and the relation between them is OneToMany.
I am trying to retrieve rooms that are not booked by check_in and check_out fields.
I did a function in RoomRepository called 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();
    }

When I call this function from the controller

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;
    }

I get an error which is :

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

I tried to fixed by change the b.room to 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();
    }

and I get another error which is :

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

which is logic because the entity bookings does not have a room.id but it has room.
I tried again to solve by changing b.room to b and the function become :

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();
    }

it worked fine without any error but it didn't retrieve the right result.
note: I tested the sql query in MySQL database and I get the right result

Haven't you asked the same thing yesterday? ([see here](https://stackoverflow.com/questions/47872448/invalid-pathexpression-must-be-a-statefieldpathexpression)). Why do you ask the same once again? I provide you with a [possible solution](https://stackoverflow.com/questions/22666692/invalid-pathexpression-must-be-a-statefieldpathexpression-in-query-builder-wi) if it didn't work then create a new answer with a different title and better readability. Voting this to be closed!! — ReynierPM, Dec 20 '17 at 15:26
@ReynierPM thank you for your contribution even it was so bad. if you don't like my posts please don't post again. — Mostafa, Dec 20 '17 at 15:38
You should read and learn how to use SO properly before post the same once and again anyways do not expect any of us to do your job what you should expect is to research and learn - something that you didn't practice often. If you take a look to my previous answer (just yesterday) and this one is exactly what I have posted as a solution on my own answer. — ReynierPM, Dec 20 '17 at 16:14

score 2 · Answer 1 · answered Dec 20 '17 at 15:35

2

I just resolved my problem by adding 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);

answered Dec 20 '17 at 15:35

Mostafa

63
2
11

Hi @Mostafa, do you have any documentation about the use of IDENTITY()?...i Have the same problem as you and resolved used this function...thanks – gasgen Mar 27 '21 at 01:20

Invalid PathExpression must be a StateFieldPathExpression

1 Answers1