How to perform a query having pseudo-relations between two distinct database types?

Question

In a nutshell, the title best discribes my question, but here I am showing the core of the problem.

I have two databases in my web application, One is MariaDB, the other is MongoDB, To give some context, the "user" table in MariaDB stores user information with column "id" it's primary key, there is another "badge" table which stores badge information with also column "id" it's primary key, at last there is "user_badge" collection in MongoDB having documents of fields

{_id, user_id, badge_id, date}

which just links the User with his/her Badges. This is what I meant by pseudo-relation, Unfortunately I don't know what is it called in this situation.

An example: I want to query and get all users that have a badge with ID 1. So my pseudo-query should do something like "Select all fields from user table where badge_id in user_badge collection is 1". I highlighted like here because this is impossible to be done in a query (based on my knowledge) somehow a query ought to be made on the MongoDB database first then a second have to be made in the MariaDB database against the results of the former query.

Edit: My original question was about how to implement this in Yii2 PHP framework, but when I googled for sometime and I found out no information to do such a thing even in pure PHP, So I decide to end my edited question here, asking for a way to query between a table in an sql database and a collection in a no-sql database, Yet below I leave my old question which just asks for how to do this more specifically in the PHP framework. really if I knew how to do this in pure PHP I can just make a function somehow that does that in the framework if there wasn't any.

Obviously there cannot be a direct primarykey-foriegnkey relation between two database types but I overrided this issue by having a ::hasMany ActiveRecord method in my User Model, and that worked perfectly fine; When I have a User model between hands I just call $model->userBadges to get from MongoDB all documents having that User ID, also vice versa. The problem is when I do a Query involving this relation, I get error

Calling unknown method: yii\mongodb\ActiveQuery::getTableNameAndAlias()

Parts of my Application

User getUserBadges method in User model

public function getUserBadges(){
    return $this->hasMany(UserBadge::className(), ['user_id' => 'id']);
}

UserBadge model extending yii\mongodb\ActiveRecord

class UserBadge extends ActiveRecord
{
    public static function collectionName()
    {
        return 'user_badge';
    }

    public function attributes()
    {
        return ['_id', 'user_id', 'badge_id', 'date'];
    }

    public function getUser(){
        return $this->hasOne(User::className(), ['id' => 'user_id']);
    }

    public function getBadge(){
        return $this->hasOne(Badge::className(), ['id' => 'badge_id']);
    }
}

My query

$query = User::find()->joinWith(['userBadges']);

Edit: I figured out that the previous query is not really what I want, I simplified it to be clear but the real query that I want to do and you will get the point of why I am doing all of this is

$query = User::find()->joinWith(['userBadges'])->where(['badge_id' => 1]);

And with that I can get users from the user table who have a certain badge with id for example 1.

And here the code fails and throws the error stated above. After inspecting for sometime I found the API for the joinWith method

This method allows you to reuse existing relation definitions to perform JOIN queries. Based on the definition of the specified relation(s), the method will append one or multiple JOIN statements to the current query.

And here I knew that it's normal for this error to occur, In my query I am joining a document in a collection of the MongoDB database not a record in a table in a SQL database which definitely wouldn't work. I got stuck here and don't know what to exactly do, I am sticking to have user table in a SQL database and having the user_badge collection in a no-SQL database, what shall I do in such scenario? query on the no-SQL first and then query a SQL query against the result of the former query? or there is already a solution to such a problem in the methods of AcitveQuery? Or my Database structure is invalid?

Thanks in advance.

score 0 · Answer 1 · answered Mar 08 '21 at 18:17

So after some good time I knew how to do it with the help of this question, where a SQL query is made against a PHP array.

So, first MongoDB will be queried and the results will be stored in an array, then A MariaDB SQL query will be made against the array generated from former query, I am pretty sure that this is not the best option; what if the result of the MongoDB query 100,000? well an array will be made with 100,000 entries, the SQL query will be made using also that 100,000 item array. Yet this is the best answer I could get (until now).

How to implement it in Yii2

// This line query from the MongoDB database and format the data returned well
$userBadges = UserBadge::find()->select(['user_id'])->where(['badge_id' => 1])->column();

// This line make the SQL query using the array generated from the former line
$userQuery = User::find()->where(['id' => $userBadges]);

I hope there can be a better answer for this question that someone can know, But I thought of sharing what I have reached so far.

How to perform a query having pseudo-relations between two distinct database types?

1 Answers1