misterlewy1 misterlewy1 - 2 months ago 8
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(
'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

Answer

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 > ?
Comments