src/Zicht/Bundle/PageBundle/Command/CleanAliasCommand.php
protected function execute(InputInterface $input, OutputInterface $output)
    {
        $locale = $input->getArgument('locale');

        /** @var Connection $connection */
        $connection = $this->getContainer()->get('doctrine')->getConnection();
        $stmt = $connection->prepare(
            'SELECT 
                page.id as original_page_id, 
                url_alias.id, 
                url_alias.internal_url, 
                url_alias.public_url, 
                REPLACE(url_alias.internal_url, :url_pre_id, "") AS page_id 
            FROM url_alias 
            LEFT JOIN page ON page.id = REPLACE(url_alias.internal_url, :url_pre_id, \'\') 
            WHERE internal_url LIKE :url_like
            AND page.id IS NULL'
        );
        $stmt->execute(
            [
                ':url_pre_id' => sprintf('/%s/page/', $locale),
                ':url_like' => sprintf('/%s/page/', sprintf('/%s/page/', $locale) . '%'),
            ]
        );

        $table = new Table($output);
        $table->setHeaders(['Alias', 'Internal URL', 'Stripped Id', 'Original Page Id']);

        $records = $stmt->fetchAll();

        if (!count($records)) {
            $output->writeln('No obsolete aliases found!');
            return 0;
        }

        foreach ($records as $record) {
            $table->addRow(
                [
                   $record['public_url'],
                   $record['internal_url'],
                   $record['page_id'],
                   $record['original_page_id'],
                ]
            );
        }

        $table->render();

        $helper = $this->getHelper('question');
        $confirm = new ConfirmationQuestion('Would you like to remove the above obsolete url aliases? [Y/n] : ', false);

        if (!$helper->ask($input, $output, $confirm)) {
            $output->writeln('Okay, bye');
            return 0;
        }

        $stmt->execute();

        $aliasIds = [];
        foreach ($stmt->fetchAll() as $record) {
            $aliasIds[] = $record['id'];
        }

        $connection->query(sprintf('DELETE FROM url_alias WHERE id IN (%s)', implode(', ', $aliasIds)));
        $output->writeln(sprintf('Removed %d aliases', count($records)));

        return 0;
    }
protected function execute(InputInterface $input, OutputInterface $output)
    {
        $locale = $input->getArgument('locale');

        /** @var Connection $connection */
        $connection = $this->getContainer()->get('doctrine')->getConnection();
        $stmt = $connection->prepare(
            'SELECT 
                page.id as original_page_id, 
                url_alias.id, 
                url_alias.internal_url, 
                url_alias.public_url, 
                REPLACE(url_alias.internal_url, :url_pre_id, "") AS page_id 
            FROM url_alias 
            LEFT JOIN page ON page.id = REPLACE(url_alias.internal_url, :url_pre_id, \'\') 
            WHERE internal_url LIKE :url_like
            AND page.id IS NULL'
        );
        $stmt->execute(
            [
                ':url_pre_id' => sprintf('/%s/page/', $locale),
                ':url_like' => sprintf('/%s/page/', sprintf('/%s/page/', $locale) . '%'),
            ]
        );

        $table = new Table($output);
        $table->setHeaders(['Alias', 'Internal URL', 'Stripped Id', 'Original Page Id']);

        $records = $stmt->fetchAll();

        if (!count($records)) {
            $output->writeln('No obsolete aliases found!');
            return 0;
        }

        foreach ($records as $record) {
            $table->addRow(
                [
                   $record['public_url'],
                   $record['internal_url'],
                   $record['page_id'],
                   $record['original_page_id'],
                ]
            );
        }

        $table->render();

        $helper = $this->getHelper('question');
        $confirm = new ConfirmationQuestion('Would you like to remove the above obsolete url aliases? [Y/n] : ', false);

        if (!$helper->ask($input, $output, $confirm)) {
            $output->writeln('Okay, bye');
            return 0;
        }

        $stmt->execute();

        $aliasIds = [];
        foreach ($stmt->fetchAll() as $record) {
            $aliasIds[] = $record['id'];
        }

        $connection->query(sprintf('DELETE FROM url_alias WHERE id IN (%s)', implode(', ', $aliasIds)));
        $output->writeln(sprintf('Removed %d aliases', count($records)));

        return 0;
    }
{@inheritDoc}