Symfony2 / Doctrine - Joining Mysql and Sqlite Entities

Question

I'm having a problem with Symfony2 / Doctrine.

I'm starting to think that I'm trying to do the impossible, joining 2 seperate databases together via a relationship.

I've got a mysql database, and an sqlite database.
I've got 2 bundles (each talking to one of the databases each)
I've got 2x Entity Managers, with mappings working correctly.

I can access each database, via it's own Entity Manager just fine, Each bundle can access the other bundles Entity Manager, and it's all working well on that front.

The Sqlite database is locked-down to another application, and I have to use it as-is. The mysql-database is mine to do whatever I need with.

In the sqlite database I've got a "Projects" table
In the mysql database I've got a "Tasks" table.
One project can have many tasks.

My tasks table has a project_id field, which is populated with the ID from the projects table. What I'm trying to do is to make this relationship work properly, so that I can use twig to do what you normally can do with twig in more normal situations. I.e. call something like {{ project.tasks }} or {{ tasks.projects }}.

At the moment I've got some code in the Project controller, passing the Tasks to the views and vice versa. This does work, but it's quite cumbersome. What I'd really like to do is to have the ORM mapping working correctly between each entity.

Can Doctrine/Symfony2 do this, or am I trying to do the impossible?

Any assistance would be greatly appreciated.

Here's an extract from my config.yml file.

orm:
        auto_generate_proxy_classes: %kernel.debug%
        default_entity_manager:   agile
        entity_managers:
            glue:
                connection:       glue
                mappings:
                    WebplaceGlueBundle: ~
            agile:
                connection:       agile
                mappings:
                    WebplaceAgileBundle: ~

bobthecow bobthecow · Accepted Answer · 2012-01-10T23:15:22

We have a similar problem at OpenSky: we have some data stored in MongoDB, and some stored in MySQL. We use an extension to stof's DoctrineExtensions bundle:

https://github.com/opensky/DoctrineExtensionsBundle/tree/orm2odm_references_current

This lets us add @Gedmo\ReferenceOne annotations between ODM and ORM. You might be able to use this directly between two ORM connections, but if not it'll give you a starting point for handling relationships between different persistence layers...

/**
 * @Gedmo\ReferenceOne(
 *     type="document",
 *     class="MyBundle\Document\User",
 *     identifier="userId"
 * )
 */

Symfony2 / Doctrine - Joining Mysql and Sqlite Entities

1 Answers