misterlewy1 misterlewy1 - 4 months ago 12
MySQL Question

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(
FROM SITEArticlesBundle:Article
id = (SELECT id
FROM SITEArticlesBundle:Article
WHERE ? > 2 LIMIT 1)
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
id = (SELECT id
FROM ' at line 2

I would be really grateful if someone could help me.



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 < ?
 SELECT MIN(n.id) AS id
   FROM `SITEArticlesBundle:Article` n
  WHERE n.id > ?