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 ...

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.

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(),
]);