CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
user1063963
user1063963

Reputation: 1375

How to set up entity (doctrine) for database view in Symfony 2

Let's say I have a view table. And I want to get data from it to an entity. Can I (and how) create entity class to do that (no save operation needed)? I just want to display them.

Upvotes: 33

Views: 32726

Answers (7)

Alex Mantaut
Alex Mantaut

Reputation: 3885

Both the previous answers are correct, but if you use the doctrine migration tool and do a schema:update it will fail...

So, in addition to marking the entity as read only and making the constructor private (explained in Ian Phillips answer):

/**
 * @ORM\Entity(readOnly=true)
 * @ORM\Table(name="your_view_table")
 */
class YourEntity {
    private function __construct() {}
}

You would need to set the schema tool to ignore the entity when doing a schema:update...

In order to do that you just need to create this command in your bundle, and set yout entity in the ignoredEntity list:

src/Acme/CoreBundle/Command/DoctrineUpdateCommand.php:

<?php

namespace Acme\CoreBundle\Command;

use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Doctrine\ORM\Tools\SchemaTool;

class DoctrineUpdateCommand extends \Doctrine\Bundle\DoctrineBundle\Command\Proxy\UpdateSchemaDoctrineCommand {

  protected $ignoredEntities = array(
      'Acme\CoreBundle\Entity\EntityToIgnore'
  );

  protected function executeSchemaCommand(InputInterface $input, OutputInterface $output, SchemaTool $schemaTool, array $metadatas) {

    /** @var $metadata \Doctrine\ORM\Mapping\ClassMetadata[] */
    $newMetadatas = array();
    foreach ($metadatas as $metadata) {
      if (!in_array($metadata->getName(), $this->ignoredEntities)) {
        array_push($newMetadatas, $metadata);
      }
    }

    parent::executeSchemaCommand($input, $output, $schemaTool, $newMetadatas);
  }

}

(credit to Alexandru Trandafir Catalin: obtained from here: https://stackoverflow.com/a/25948910/1442457)

BTW, this is the only way I found to work with views from doctrine... I know it is a workaround... If there is a better way I am open or suggestions)

Upvotes: 22

yergo
yergo

Reputation: 4980

I spend a day on that due to the necessity to introduce a view in my database on the Zend implementation.

As all previously said, you should create an entity, and this entity must have Id() annotation:

/**
 * @Doctrine\ORM\Mapping\Table(name="your_view")
 * @Doctrine\ORM\Mapping\Entity(readOnly=true)
 */
class YourViewEntity
{
    /**
     * @var SomeEntityInterface
     * @Doctrine\ORM\Mapping\Id()
     * @Doctrine\ORM\Mapping\OneToOne(targetEntity="SomeMainEntity", fetch="LAZY")
     * @Doctrine\ORM\Mapping\JoinColumn(nullable=false, referencedColumnName="id")
     */
    protected $some;

    /**
     * @var AnotherEntityInterface
     * @Doctrine\ORM\Mapping\ManyToOne(targetEntity="AnotherEntity", fetch="LAZY")
     * @Doctrine\ORM\Mapping\JoinColumn(nullable=false, referencedColumnName="id")
     */
    protected $another;

    // Make the constructor private so that only Doctrine can create instances.
    private function __construct() {}
}

also with private constructor as described in Ian Phillips answer. Yet this does not prevent orm:schema-tool:update to create a table based on new entity, trying to override our view... Despite the fact that using orm:schema-tool:update should be avoided on production in favor of migration scripts, for development purposes this is extremely useful.

As schema_filter: ~^(?!view_)~ is both seem to not working, also deprecated, I managed to find a trick on Kamil Adryjanek page that presents option to add an EventListener or Subscriber to entity manager, that will prevent creating table for us. Mine implementation below:

class SkipAutogenerateTableSubscriber implements EventSubscriber
{
    public const CONFIG_KEY = "skip_autogenerate_entities";

    private $ignoredEntities = [];

    public function __construct($config)
    {
        if (array_key_exists(self::CONFIG_KEY, $config)) {
            $this->ignoredEntities = (array) $config[self::CONFIG_KEY];
        }
    }

    public function getSubscribedEvents()
    {
        return [
            ToolEvents::postGenerateSchema
        ];
    }

    public function postGenerateSchema(GenerateSchemaEventArgs $args)
    {
        $schema = $args->getSchema();
        $em = $args->getEntityManager();

        $ignoredTables = [];
        foreach ($this->ignoredEntities as $entityName) {
            $ignoredTables[] = $em->getClassMetadata($entityName)->getTableName();
        }

        foreach ($schema->getTables() as $table) {

            if (in_array($table->getName(), $ignoredTables)) {
                $schema->dropTable($table->getName());
            }
        }
    }
}

And this solves problem not only for orm:schema-tool, but also for migrations:diff of doctrine/migrations module.

Upvotes: 2

Shadi Akil
Shadi Akil

Reputation: 383

In addition to above answer, You must have a naming Strategy of your view' entities and the virtual tables, for example: view_your_table, and then you must add the following code to the doctrine.yaml, to disable creating new migration file to the view: schema_filter: ~^(?!view_)~

Upvotes: 2

sebastien hupin
sebastien hupin

Reputation: 11

In addition to above answer, I have mixed some of your example code to extend the DoctrineUpdateCommand

This is my DoctrineUpdateCommand:

class DoctrineUpdateCommand extends UpdateSchemaDoctrineCommand{
   protected function executeSchemaCommand(InputInterface $input, OutputInterface $output, SchemaTool $schemaTool, array $metadatas) {
      $container = $this->getApplication()->getKernel()->getContainer();  

     $filterExpr = $container->get('doctrine')->getEntityManager()->getConnection()->getConfiguration()->getFilterSchemaAssetsExpression();
     $emptyFilterExpression = empty($filterExpr);

     /** @var $newMetadatas \Doctrine\ORM\Mapping\ClassMetadata */
     $newMetadatas = array();

     foreach ($metadatas as $metadata) {
        if(($emptyFilterExpression||preg_match($filterExpr, $metadata->getTableName()))){
            array_push($newMetadatas, $metadata);
        }        
     }

     parent::executeSchemaCommand($input, $output, $schemaTool, $newMetadatas);
 }
}

Thanks for the right way

Upvotes: 1

bekco
bekco

Reputation: 531

In addition to above anwers if you are using doctrine migrations for schema update the following configuration works perfectly.

/**
 * @ORM\Entity(readOnly=true)
 * @ORM\Table(name="view_table_name")
 */
class YourEntity {
    private function __construct() {}
}

Till here is te same as above answers. Here you need to configure doctrine not to bind schemas; 

doctrine:
    dbal:
        schema_filter: ~^(?!view_)~

The above filter definition filters all 'view_' prefixed tables as well as views an could be extended using regex. Just make sure you have named your views with 'view_' prefix.

But doctrine:schema:update --dump-sql still shows the views, I hope they will integrate the same filter to schema update too.

I hope this solution would help some others.

Source: http://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html#manual-tables

Upvotes: 24

Ian Phillips
Ian Phillips

Reputation: 2057

The accepted answer is correct, but I'd like to offer some additional suggestions that you might want to consider:

Mark your entity as read-only.

Make the constructor private so that only Doctrine can create instances.

/**
 * @ORM\Entity(readOnly=true)
 * @ORM\Table(name="your_view_table")
 */
class YourEntity {
    private function __construct() {}
}

Upvotes: 50

Elnur Abdurrakhimov
Elnur Abdurrakhimov

Reputation: 44851

There is nothing special in querying a view — it's just a virtual table. Set the table of your entity this way and enjoy:

/**
 * @ORM\Entity
 * @ORM\Table(name="your_view_table")
 */
class YourEntity {
    // ...
}

Upvotes: 29

Related Questions