Write a simple query and solve: [Syntax Error] line 0, col 7: Error: Expected IdentificationVariable

Question

I've this error and I would like understand:

[Syntax Error] line 0, col 7: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got 'SELECT'

It's because I have do this:

public function findByBookings($start, $leave): array
{
    $qb = $this->createQueryBuilder('SELECT * FROM room as r JOIN booking as b ON r.id = b.r WHERE b.r NOT IN 
    (SELECT b.room FROM b WHERE b.startDate <= :startDate AND b.leaveDate >= :leaveDate, OR WHERE b.startDate >= :startDate AND b.leaveDate <= :leaveDate, OR WHERE b.startDate >= :startDate AND b.leaveDate >= :leaveDate, OR WHERE b.startDate <= :startDate AND b.leaveDate <= :leaveDate)')
        ->setParameter(':start', $start)
        ->setParameter(':leave', $leave);

    $query = $qb->getQuery();

    $results = $query->getResult();

    return $results;

  }

If I change $this->createQueryBuilder for $this->createQuery() I've another error:

Undefined method "createQuery". The method name must start with either findBy, findOneBy or countBy!

Why Symfony return me this error where as the name of my function begin by "findBy"?

Thank you

Answer 1

Here is a tree of a bare minimum setup to reproduce an environment to test my modifications to your dql query.

.
├── composer.json
├── composer.lock
├── init.sql
└── src
    ├── Entity
    └── index.php

composer.json

{
  "name": "oluwafemi.sule/experiment",
  "type": "project",
  "require": {
    "symfony/doctrine-messenger": "^6.2",
    "doctrine/orm": "^2.14",
    "doctrine/annotations": "^2.0"
  },
  "autoload": {
    "psr-4": {
      "Experiment\\": "src/"
    }
  },
  "authors": [
    {
      "name": "Oluwafemi Sule"
    }
  ]
}

A room could have one or more bookings.

src/Entity/Booking.php

<?php
namespace Experiment\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="bookings")
 */
class Booking
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="datetime")
     */
    private string $startDate;

    /**
     * @ORM\Column(type="datetime")
     */
    private string $leaveDate;

    /**
     * @ORM\ManyToOne(targetEntity="Room")
     * @ORM\JoinColumn(name="room", referencedColumnName="id")
     */
    private Room|null $room = null;
}

src/Entity/Room.php

<?php
namespace Experiment\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="rooms")
 */
class Room
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;
}

src/index.php

<?php
require __DIR__.'/..'.'/vendor/autoload.php';

use Doctrine\DBAL\DriverManager;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Configuration;
use Doctrine\ORM\Mapping\Driver\AnnotationDriver;
use Doctrine\Common\Annotations\AnnotationReader;

$params = [
    'password' => 'pass',
    'host' => '0.0.0.0',
    'dbname' => 'test',
    'user' => 'postgres',
    'driver' => 'pdo_pgsql',
];
$config = new Configuration();
$config->setAutoGenerateProxyClasses(true);
$config->setProxyNamespace('Experiment');
$config->setProxyDir(sys_get_temp_dir());
$config->setMetadataDriverImpl(new AnnotationDriver(new AnnotationReader()));

$dql = <<<EOL
SELECT c.id FROM Experiment\Entity\Booking AS c JOIN c.room AS s
WHERE s.id NOT IN (SELECT r.id
     FROM Experiment\Entity\Booking AS b JOIN b.room AS r
     WHERE 
       b.startDate <= :startDate AND b.leaveDate >= :leaveDate OR  
       b.startDate >= :startDate AND b.leaveDate <= :leaveDate OR 
       b.startDate >= :startDate AND b.leaveDate >= :leaveDate OR
       b.startDate <= :startDate AND b.leaveDate <= :leaveDate)
EOL;

$connection =  DriverManager::getConnection($params);
$em = EntityManager::create($connection, $config);

$queryBuilder = $connection->createQueryBuilder();


$start = '2020-05-02';
$leave = '2020-05-02';

$query = $em
     ->createQuery($dql)
     ->setParameter('startDate', $start)
     ->setParameter('leaveDate', $leave);

$results = $query->getResult();

var_dump($results);

init.sql

create table rooms (id SERIAL);

create table bookings (
    id SERIAL,
    startDate timestamp,
    leaveDate timestamp,
    room integer
);

INSERT INTO
    rooms
VALUES
    (1),
    (2),
    (3);

INSERT INTO
    bookings
VALUES
    (1, '2023-05-01T01:00', '2023-05-01T03:00', 1),
    (2, '2023-05-01T05:00', '2023-05-01T09:00', 2),
    (3, '2023-05-03', '2023-05-04', 3);

• Run composer install

• Run the Postgres service in Docker

docker run -it --rm --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=pass -e POSTGRES_DB=test -v $(PWD)/init.sql:/docker-entrypoint-initdb.d/init.sql postgres

Then run php src/index.php