CODEWITHSUNDEEP
mysqlmongodb
georgesamper
georgesamper

Reputation: 5179

MySQL to MongoDB

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?

Upvotes: 1

Views: 1110

Answers (3)

Tyler Brock
Tyler Brock

Reputation: 30176

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.

Upvotes: 1

Petrogad
Petrogad

Reputation: 4423

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.

Upvotes: 2

maerics
maerics

Reputation: 156642

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.

Upvotes: 1

Related Questions