How to stop doctrine trying to create a table for a view that has been mapped on an entity?

Question

How I can stop symfony trying to create the table for the view I created on a doctrine migration?

Entity mapping the view

/**
* Class TenancyPendingInspection
* @ORM\Entity(repositoryClass="DJABundle\PropertyVisit\Repository\TenancyPendingInspectionRepository", readOnly=true)
* @ORM\Table(name="view_tenancies_pending_inspections")
*/
class TenancyPendingInspection
{

I have as well the doctrine migration file.

Doctrine Configuration

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                [...]
                charset:   UTF8
                server_version:       5.6
                schema_filter: ~^(?!view_)~

Doctirne schema validate

php app/console doc:sch:val
[Mapping]  OK - The mapping files are correct.
[Database] FAIL - The database schema is not in sync with the current mapping file.

Doctrine schema update

php app/console doc:sch:update --dump-sql
CREATE TABLE view_tenancies_pending_inspections ...

Sexagesima

I've tried '/^(?!view_)/' and it work as expected. What version of Doctrine-DBAL are you installed? – Seine

I also configured it under dbal: key directly. – Seine

DBAL v2.5.13 As I understood this filter stop DBAL viewing that tables (view_*) so Doctrine tries to create the table all the time – Sexagesima

You might need to remove the ORM annotations too, @ORM\Entity and @ORM\Table. – Seine

Answer 1

Short answer: It can't be done.

This happens because MysqlPlatform ignores the views.

//vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php

class MySqlPlatform extends AbstractPlatform
[...]
    public function getListTablesSQL()
    {
         return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";
    }
[...]

Solution: Create a new MysqlPlatform that does not ignore the views:

class MysqlViewsPlatform extends \Doctrine\DBAL\Platforms\MySqlPlatform
{
    public function getListTablesSQL()
    {
        return "SHOW FULL TABLES";
    }
}

Create a service with your platform:

services:
    doctrine.dbal.mysql_views_platform:
        class: albertsola\DoctrineViews\MysqlViewsPlatform
        arguments: []

Use that platform with your connection:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   "%database_driver%"
                host:     "database_host%"
                port:     "database_port%"
                dbname:   "database_name%"
                user:     "database_user%"
                password: "database_password%"
                charset:   UTF8
                platform_service: "doctrine.dbal.mysql_views_platform"

app/console doctrine:schema:validate This command validates that the entity and the view entity are in sync.

Side effects: app/console doctrine:schema:update --dump-sql If the view and the entity are not in sync this will generate SQL that should not be executed! You have to manually update your view on your database.

I use doctrine migrations that solves this. Although doctrine:schema:update --dump-sql is quite useful to check what it is not matching in your view/entity.

NOTE: This hack stops creating the table but doctrine schema diff still try to update the "table" adding the foreign keys.

Sexagesima

Answer 2

This works well for me;

class DiffCommandWrapper extends DiffCommand
{
    private $views = [];

    public function __construct(\PDO $db, SchemaProviderInterface $schemaProvider = null)
    {
        parent::__construct($schemaProvider);

        $name = $db->query('select database()')->fetchColumn();

        $stm = $db->query("SHOW FULL TABLES IN {$name} WHERE TABLE_TYPE LIKE 'VIEW';");

        foreach ($stm->fetchAll(\PDO::FETCH_ASSOC) as $row) {
            $this->views[] = $row["Tables_in_{$name}"];
        }
    }

    public function execute(InputInterface $input, OutputInterface $output)
    {
        $input->setOption('filter-expression', '/!(' . implode('|', $this->views) . ')/');
        parent::execute($input, $output);
    }
}

Then use the wrapper in place of the DiffCommand in your cli-config.php

ConsoleRunner::addCommands($cli);
$cli->addCommands([
    new something\DiffCommandWrapper($connection),
    new Command\ExecuteCommand(),
    new Command\GenerateCommand(),
    new Command\MigrateCommand(),
    new Command\StatusCommand(),
    new Command\VersionCommand(),
]);

Siloxane

Answer 3

You should be able to use setFilterSchemaAssetsExpression.

http://www.doctrine-project.org/api/dbal/2.4/source-class-Doctrine.DBAL.Configuration.html#87-99

Failing that, if you'd like to do less programming and more config, you can put the view into another entity manager. Not the nicest way though....

https://symfony.com/doc/3.3/doctrine/multiple_entity_managers.html

Lohner

1) $conf->setFilterSchemaAssetsExpression() filters which tables doctrine is allowed to see from the database so it can't be used for this purpose as doctrine keeps trying to create that table 2) Registering it in a different entity manager/connection it is a interesting approach but then I will not have access to the rest of the entities that I'm linking from the view. So any of those solutions fix this issue – Sexagesima

Answer 4

Since doctrine-orm 2.11 it's possible to ignore selected Entities, see here.

In doctrine yaml configuration you can specify it as below:

doctrine:
    orm:
        # ....
        schema_ignore_classes:
          - 'App\Entity\TenancyPendingInspection'
          - 'App\Entity\ViewToBeIngored'

Monarchal