Symfony DQL syntax error while selecting last and next record

I want to get next and previous record in my database, this is my first time using DQL and I don't really understand why it doesn't work.

Here's how my custom query look like:

    $em = $this->getDoctrine()->getManager();
    $rsm = new ResultSetMapping();
    $rsm->addScalarResult('id', 'id');

    $query = $em->createNativeQuery(
        'SELECT id 
        FROM SITEArticlesBundle:Article
        WHERE
            id = (SELECT id
            FROM SITEArticlesBundle:Article
            WHERE ? > 2 LIMIT 1)
        OR
            id = (SELECT id 
            FROM SITEArticlesBundle:Article
            WHERE ? < 2 LIMIT 1)', $rsm);
    $query->setParameter(1, $id);
    $query->setParameter(2, $id);
    $nextAndPrevNews = $query->execute();

I want to get array of 2 id, like that:

[
    ['id' => 1]
    ['id' => 3]
]

But I get a SQL syntax error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':Article WHERE id = (SELECT id FROM ' at line 2

I would be really grateful if someone could help me.

Thanks

Solution

One issue is the colon character in the table name. That character isn't allowed, unless the table name is escaped.

I suspect that SITEArticlesBundle:Article isn't the actual identifier for the MySQL table. In a native query, you need to specify the actual name of the MySQL table.

If that is your tablename, then you can enclose it in backtick characters:

 FROM `SITEArticlesBundle:Article`

I'm not sure why you'd be comparing the value of $id with a literal 2, and not comparing that to the value of the id column in the table. I don't get it.

Unless you are trying to pass in the column name as a bind parameter, which won't work. You can only pass in values to a prepared SQL statement, you can't pass identifiers or keywords or other SQL constructs in as bind parameters.

Personally, I'd avoid the OR condition and the subqueries, and just use a UNION ALL operation.

 SELECT MAX(p.id) AS id
   FROM `SITEArticlesBundle:Article` p
  WHERE p.id < ?
  UNION ALL 
 SELECT MIN(n.id) AS id
   FROM `SITEArticlesBundle:Article` n
  WHERE n.id > ?