0

I have view table in my Database, how can I retrieve the data from this views?

I tried to use

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createQuery('SELECT * FROM my_views');
$result = $query->getResult();

but it doesn't work.

monkeyUser
  • 4,301
  • 7
  • 46
  • 95
  • What you have written inside your `createQuery` is native SQL while `createQuery` expects [DQL](http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html) statements. – Dipen Shah Jul 21 '15 at 15:11
  • possible duplicate of [Execute raw SQL using Doctrine 2](http://stackoverflow.com/questions/3325012/execute-raw-sql-using-doctrine-2) – Francesco Casula Jul 21 '15 at 15:17
  • 1
    you can use native sql and map it to an entity. see the [doc](http://docs.doctrine-project.org/en/latest/reference/native-sql.html#the-nativequery-class) – Matteo Jul 21 '15 at 15:17
  • @Matteo In this way, can i have also getter methods? – monkeyUser Jul 21 '15 at 15:20
  • 1
    yes, see this [examples](http://docs.doctrine-project.org/en/latest/reference/native-sql.html#examples) on the doc – Matteo Jul 21 '15 at 15:26
  • @Matteo I tried your solution, I have to create an empty entity with getter and setter methods right? – monkeyUser Jul 22 '15 at 08:18
  • 1
    Hi @monkeyUser, yes, if you want to map the result in an object you need to write the relative class or **use an existing one and map the field as you want** – Matteo Jul 22 '15 at 08:48

1 Answers1

3

If you want to perform a simple SQL query, you can do that :

$con = $this->getDoctrine()->getEntityManager()->getConnection();
$stmt = $con->executeQuery('SELECT * FROM my_views');
foreach ($stmt->fetchAll() as $row){
   print_r($row);
}

When you use $em->createQuery(), you need to work with Doctrine entities.

If you want to use the mapping with your view, juste create an entity :

namespace Your\Bundle\Entity;

/**
 * @ORM\Entity
 * @ORM\Table(name="my_view")
 */
class MyView
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     */
    protected $id;

    /**
     * @ORM\Column(type="string")
     */
    protected $someVarcharColumn;

    public function getId()
    {
        return $this->id;
    }

    public function getSomeVarcharColumn()
    {
        return $this->someVarcharColumn;
    }
}

And you can query it with DQL like this :

$results = $em->createQuery('
   SELECT v
   FROM YourBundle:MyView v
')->getResult();
Raphaël Malié
  • 3,912
  • 21
  • 37
  • so nice, Can I do also a mapping on Views? I'd like to have getter methods – monkeyUser Jul 21 '15 at 15:16
  • Thanks for your time. I have to create an entity in "manually" way, I can't have this entity with some function? – monkeyUser Jul 21 '15 at 15:30
  • You can generate entities from the database, but I'm not familiar with this, so you should read the documentation here : http://symfony.com/doc/current/cookbook/doctrine/reverse_engineering.html – Raphaël Malié Jul 21 '15 at 15:32
  • I already used http://symfony.com/doc/current/cookbook/doctrine/reverse_engineering.html for "classic" table, I don't know if is good also for the views... Thanks again ! – monkeyUser Jul 21 '15 at 15:34
  • After I create the view, I run schema:update and cache:clear and composer update. But it gives me an error No mapping file found named 'MyView.orm.yml' for class 'YourBundle\Entity\MyView. I am using Symfony 2.8...Thanks in advance. – aniruddha Feb 04 '17 at 17:35
  • First of all thanks to Raphaël Malié for this answer. I found the answer here(http://stackoverflow.com/questions/19499176/doctrine-2-generate-entities-with-views-from-database). I created a mapping file and it works. – aniruddha Feb 04 '17 at 17:36