'How to implement joins between mongo collection and mysql table

I am working on a website where I have 1 table which contain all posts/thread related information and all the "group" posts related information is stored in mongodb due to its huge structure.

Everything is working great. Now I have to add filters on the groups posts. For that I need to fetch information from both the "Group" Posts collections in mongo as well as "posts" table from mysql.

Let me show you 1 document from "Groups" collection:

{
    "_id" : NumberLong("5834246781632"),
    "created_by" : "3691203254400",
    "title" : "some title here",
    "lowcase_title" : "some title here",
    "date" : "2015-12-24 11:50:42",
    "cat" : "724",
    "description" : "dasf",
    "members" : ["3691203254400", "3693509330048"],
    "posts" : ["1471301175296", "1473939604352", "1479643049536"],
    "is_deleted" : NumberLong(0)
}

and Post table contains id, title, created_date, created_by.. etc columns.

I need to add a filter to sort "groups" by number of "posts" posted in group in last 2 weeks. ( in Group collection above the "posts" sub-document array contain posts ids contain in that group)

If I can join these 2 tables then I can add filter easily.

I am working in Yii 1.x.

Can anyone guide me better?

phpmysqlmongodb


Solution 1:[1]

MySQL and MongoDB are completely separated technologies which have no way to communicate with each other, so you won't get around implementing the JOIN on the application-side. That means you have to query one database first and then use the results to query the other. The one you query first should be the one you can filter the most to avoid loading too much unnecessary data into your application.

Solution 2:[2]

I know this question is old, but It could be helpful for others.

I would suggest using a data lake platform like Dremio. It allows a sort of abstraction on the database source, with SQL-like query language.

You could find other platforms but I am suggesting Dremio as an example.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Philipp
Solution 2 MOHAMAD MONZER

Related Questions