MongoDB + MySQL with Doctrine 2. Example of listener to maintain redundant data (alternative to joins)

If you are developing an application with MongoDb, sooner or later you will face the need of solving a query that involves more than one collections (or a MongoDB collection with a MySQL table). In this case, an SQL join operation won’t be available.

If you have a small amount of data, you can perform two MongoDB/MySQL queries and then join the results at the application level, but – depending on your needs – that could be not feasible or turn out to be inconvenient. A possible better solution for that could be redundancy.

Example

Consider this data model

Post (MongoDB) with million documents <–  N: 1 –> Source (MySQL table) with a few tens of thousands of records

Now, the majority of the queries act on Post but also need to join Source (example, to filter or order by Source.enabled).
How to solve efficiently ?
Embed a document into Post with the needed information of the Source table.

Usage of Doctrine 2 to automatically update the redundant data

Embedding redundant data is easy with Doctrine 2, but to keep consistency, you have to ensure any update on the source table is propagated to all the embedded documents. Doctrine 2 offers an elegant solution to the problem, using an event listener, also available for MongoDB ODM.

In this example, A change on the Source table of the redundant field (enabled), will result into an update query for the post collection. When the source.enabled field change is detected by Doctrine 2, I’ll just run an update for all the document that embed that source (equivalent to a MongoDB query like
db.post.update({“source.id”: X}, {$set: {“source.enabled”: Y}}) ).

Note that in this example I’m catching the even for a MySQL table and launch and update on a MongoDB  collection. The same logic can be implemented with MongoDB->MongoDB redundancy.

Tested with Doctrine 2.3.x-dev and Symfony 2.1.x-dev