CODEWITHSUNDEEP
phpmysqlmongodbdatabase
avex
avex

Reputation: 1

Hybrid query with MongDB and MySQL

I have a contacts table in a mysql database and an event/action log database in mongodb. Items in action log belongs contacts.

Contacts table

id | name | email
 1 | Joe  | [email protected]

Events collection

{_id:..., contact_id: 1, type:"purchase", data:{}}
{_id:..., contact_id: 1, type:"signup", data:{}}
{_id:..., contact_id: 1, type:"click", data:{}}

Query 1 - Contacts who "purchase"

This is very easy since, I just need to run a mongo .find({type:"purchase"}), iterate through contact ids and query the mysql database.

Query 2 - Contacts who didn't purchase

This is where I'm stuck, only option that came to my mind was doing .find({type:"purchase"}), grab the contact ids and put them inside a NOT IN(), but it's not going to work for the long run, what would be scalable approach to run a query like this?

Query 3 - Contact's who signup who's email starts with "joe"

Same problem as again, first I need to run the mysql part and get contacts who's email starts with "joe" and then use those ids in mongo, or reverse which ever the way where there's less data (contact ids)

Everything in mongo

I'm not sure if moving the contacts table to a mongo collection will make it easy since in mongo this kind of relationship still requires multiple query runs.

Thanks for the input.

Upvotes: 0

Views: 84

Answers (2)

user2663634
user2663634

Reputation: 1

If all your data are in mysql, then you need a JOIN operation to finish it in one query. Mongodb is not support JOIN. Maybe you can redundancy the CONTACTS data to Events.

Upvotes: 0

duffymo
duffymo

Reputation: 309008

It'd be either/or for me, not both. Logging databases should be separate from transactional ones. If you can't put everything in MongoDB, I'd have all the transactional stuff in MySQL. Duplicate the data in both, do the transactional JOINs in MySQL, and don't mingle the two when querying.

Upvotes: 1

Related Questions