CODEWITHSUNDEEP
symfonydoctrine-ormsymfony-2.3
relez
relez

Reputation: 790

Issue with a native query in Symfony2

there, I am trying to execute a native MySQL query in Symfony2, but I get a SQL error, this is the original query:

$sql= "SELECT s.id_tariffs_key,s.description,s.voice_rate
  FROM tariffs s
  JOIN (SELECT MAX(id_tariffs_key) AS id FROM tariffs GROUP BY description) max
  ON s.id_tariffs_key = max.id
  WHERE s.description like '$letter%' and s.description not like '%-%'";

This is the query for Symfony2:

$rsm = new ResultSetMapping;
  $rsm->addEntityResult('MyBundle:Tariffs', 't');

  $query = $this->getEntityManager()->createNativeQuery("SELECT t.id_tariffs_key,t.description,t.voice_rate FROM tariffs 
      JOIN (SELECT MAX(t.id_tariffs_key) AS id FROM tariffs GROUP BY t.description) max ON id_tariffs_key = max.id 
       t.description like '?%' and t.description not like '%-%'", $rsm);
  $query->setParameter(1, $letter);

When I try to load the page, it says:

An exception occurred while executing 'SELECT t.id_tariffs_key,t.description,t.voice_rate FROM tariffs 
JOIN (SELECT MAX(t.id_tariffs_key) AS id FROM tariffs GROUP BY description) max ON id_tariffs_key = max.id 
t.description like '?%' and t.description not like '%-%'' with params [23]:

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 't.description like '?%' and t.description not like '%-%'' at line 3

Any help, please?

Thanks!

Upvotes: 1

Views: 277

Answers (1)

Tom Corrigan
Tom Corrigan

Reputation: 394

You mangled your query quite severely in translating it to Doctrine including forgetting the WHERE statement.

Here's my fixed version of your Doctrine query

$query = $this->getEntityManager()->createNativeQuery(
    "SELECT t.id_tariffs_key, t.description, t.voice_rate
    FROM tariffs t
    JOIN (
        SELECT MAX(id_tariffs_key) AS id
        FROM tariffs
        GROUP BY description
    ) max ON id_tariffs_key = max.id
    WHERE t.description LIKE ? AND t.description NOT LIKE '%-%'",
    $rsm);
$query->setParameter(1, $letter.'%');

Upvotes: 1

Related Questions