MySQL to MongoDB

Question

I have this query:

        SELECT DISTINCT 1 as table_id, users.id, users.username, a.titles_name title1, b.titles_name as title2, contacts.accepted
        FROM users
        LEFT JOIN contacts ON users.id = contacts.contact_id
        LEFT JOIN titles as a ON a.id = users.title_1
        LEFT JOIN titles as b ON b.id = users.title_2
        WHERE contacts.request_id = ' + $this->session->userdata('user_id') . '
        UNION DISTINCT
        SELECT DISTINCT 2 as table_id, users.id, users.username, a.titles_name title1, b.titles_name as title2, contacts.accepted
        FROM users
        LEFT JOIN contacts ON users.id = contacts.request_id
        LEFT JOIN titles as a ON a.id = users.title_1
        LEFT JOIN titles as b ON b.id = users.title_2
        WHERE contact_id = ' . $this->session->userdata('user_id')

Would it be possible to do something similar in mongodb?

possible duplicate of [MongoDB and "joins"](http://stackoverflow.com/questions/4067197/mongodb-and-joins) — maerics, Dec 09 '11 at 18:08

score 2 · Answer 1 · answered Dec 09 '11 at 18:13

Honestly you're going to want to do things differently in mongo then in a relational DB. You can't really do a direct translation from Mongo DB to MySQL as things in Mysql just don't make sense to structure your data like that in MongoDB.

Really what you should be doing is creating a better schema for the data which fits in the paradigm of Mongo so that you can utilize everything in it.

At a direct answer, yes you can do linking by the client but there isn't a direct concept of joining.

score 1 · Answer 2 · answered Dec 09 '11 at 18:12

1

Yes and no.

Yes, because you can achieve a similar effect by simple/direct linking by the client.

No, because MongoDB has no concept of "joining" records from different collections on the server itself.

answered Dec 09 '11 at 18:12

maerics

151,642
46
269
291

score 1 · Accepted Answer · answered Dec 09 '11 at 18:19

Lets break down the components of the query:

SELECT DISTINCT 1 as table_id, users.id, users.username, a.titles_name title1, b.titles_name as title2, contacts.accepted
FROM users

Yes, this is just a distinct query asking for a subset of fields. Mongo does that.

LEFT JOIN contacts ON users.id = contacts.contact_id
LEFT JOIN titles as a ON a.id = users.title_1
LEFT JOIN titles as b ON b.id = users.title_2
WHERE contacts.request_id = ' + $this->session->userdata('user_id') . '

This is a join, which you cannot do in mongo. However, you can de-normalize so it becomes unnecessary or do similar joining of data on the client side.

UNION DISTINCT
SELECT DISTINCT 2 as table_id, users.id, users.username, a.titles_name title1, b.titles_name as title2, contacts.accepted
FROM users
LEFT JOIN contacts ON users.id = contacts.request_id
LEFT JOIN titles as a ON a.id = users.title_1
LEFT JOIN titles as b ON b.id = users.title_2
WHERE contact_id = ' . $this->session->userdata('user_id')

This is a union, which you could do on the client side and for which the query contains the same components i've described above.

TLDNR: No, you can't do this exact query in mongodb, but you can achieve the same result.

Thank you for a good explanation. So I either have to do more queries or de-normalize. I have a lot of joins in my queries. Linking for example a number to a county. One table contains all the counties with an id, and in the users table i just store the number and then join. So there are a lot of long tables that gets joined. Would i be better of sticking to mysql db in that case? Or are there any benefits for me to change to mongo. Im making an app using nodejs and expressjs. — georgesamper, Dec 09 '11 at 18:44
It depends, if de-normalizing makes sense and data that you would have joined is not duplicated a lot then it might be good for you. On the other hand, if it causes you to repeat data over and over, that is a good clue that you will need to either use a separate collection and do the join client side (or with an odm) or use a sql solution. It really depends on how many joins you are doing and if you really need to do them. (this, of course, is extremely app dependent) — Tyler Brock, Dec 09 '11 at 18:48

MySQL to MongoDB

3 Answers3