Symfony2 Doctrine2 native queries basics

Question

I am developing a basic web-app in my job. I have to work with some sql server views. I made the decision of trying native queries, and once tested it's functionality, try to write some classes to code all the queries and kinda forget their implementation.

So my issue is, I've got an Entity in Acme/MyBundle/Entity/View1.php. This entity has got all the attributes matching the table and also it's getters and setters. I guess this entity is well mapped to the DB (Doctrine cant work with views easily).

My aim is to let a Controller be able to fetch some data from those views(SQL SERVER) and return it to the view (twig) so it can display the info.

  $returned_atts = array(
    "att1" => $result[0]->getAttribute1(), //getter from the entity
    "att2" => $result[1]->getAttribute2(), //getter from the entity
  );

  return $returned_atts;`$sql = "SELECT [Attribute1],[Attribute2],[Attribute3] FROM [TEST].[dbo].[TEST_VIEW1]"; //THIS IS THE SQL SERVER QUERY
  $rsm = new ResultSetMapping($em); //result set mappin object
  $rsm->addEntityResult('Acme\MyBundle\Entity\View1', 'view1'); //entity which is based on
  $rsm->addFieldResult('view1', 'Attribute1', 'attribute1'); //only choose these 3 attributes among the whole available
  $rsm->addFieldResult('view1', 'Attribute2', 'attribute2');
  $rsm->addFieldResult('view1', 'Attribute3', 'attribute3');
  //rsm built
  $query = $em->createNativeQuery($sql, $rsm); //execute the query
  $result = $query->getResult(); //get the array

It should be possible to return the array straight from the getResult() method isn't it? And what's killing me, how can I access the attribute1, attriute2 and attriute2?

  $returned_atts = array(
    "att1" => $result[0]->getAttribute1(), //getter from the entity 
    "att2" => $result[1]->getAttribute2(), //getter from the entity
  );

  return $returned_atts;`

Answer 1

If you want result as array, you don't need to use ResultSetMapping.

$sql = " SELECT * FROM some_table";
$stmt = $this->getDoctrine()->getEntityManager()->getConnection()->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

That is a basic example for controller action. You can dump the result, use var_dump(), to see how to access your particular field values.

More examples here Doctrine raw sql