Allow to join models between primary keys and foreign keys

[crustamet]

So i have searched a lot on internet for some kind of ORM but with only Models and not some crazy thousands of lines of code just to join some models between.

I would really like to see some kind of a way joining models in one object variable or an array

for example i have two tables

users users_details

that has same primary key user_id

For now i always can Left Join with query builder but if the table changes i need to change the join also I would like to use the Entity Class also for this kinda join

So i have two Models UserModel & UserDetailsModel that specify different tables but specify same primary_key

My code look like this

public function findAllDetails()
{
    $UsersDetailsModel = new UsersDetailsModel();

    $res = $this->findAll();

    $id_users = array_column($res, $this->primary_key);

    $userDetails = $UsersDetailsModel->whereIn($this->primary_key, $id_users)->findAll();

    $userDetailsGr = array_group_by_one($userDetails,$this->primary_key);
    // this array_group function meaning will get grouped by the primary key all results from usersdetails table
            // and i have another function called array_group_by() , if there are multiple arrays on one object

    $return = [];
    foreach($res as $k=>$user)
    {
        $return[$k] = $user;

        if(isset($userDetailsGr[$user[$this->primary_key]]))
        {
            $return[$k]['details'] = $userDetailsGr[$user[$this->primary_key]];
        }
        else
        {
            $return[$k]['details'] = NULL;
        }
    }

    return $return;
}

I will like to use instead of this crazy code in my model moved another type of code in my controller

$usersModel = new UsersModel();
$UsersDetailsModel = new UsersDetailsModel();
$usersModel->where('status', 1)->with($UsersDetailsModel, 'primary_key')->findAll();
// for the second argument of method with should be the foreign key with is in relation from the model
// maybe you have different tables with another primary key and your foreign key is in relation with the model
    // so you specify that in the Model itself

This cases scenario will be so useful if it were implemented. Please guys who has this in mind it should work already please someone implement this with method.

It doesn't need to work exactly i say it should, but please don't force me to use Left Joins on my table, i only have this solution to fast solve my relations, and the slower one is what i posted uptop.

For sure it must be a fast solution without Left joins.

[lonnieezell]

Unfortunately we cannot implement a partial solution. Any solution would have to account for one-to-one, one-to-many, and many-to-many relations at the very least. And if we only support reading many would find that disappointing as well.

There are other options than left joins, though. You can take your ids of the parent models, do a single query to get all of the child models, and then insert them back into the parent models. It's relatively painless and fast.

You can find a partially finished task like you're asking about in this experiment I was playing with at one point.

Additionally, our own @MGatner has put together codeigniter4-relations which sounds pretty close to what you're looking for.

[MGatner]

Thanks for the shoutout! There are quite a few solutions if you search around on the forum. Tatter\Relations does indeed provide this as well, even using the with() method.

[crustamet]

I think we only need one-to-to and one-to-many because if we have this we then specify inside the with other models where you specify other one-to-many relations

[crustamet]

And yes i know the Tatter\Relations Library, i have tested it, the problem it is while the traits are so simple it requires schemas which does some really crazy shit, i am sorry for tatter/relations, I don't seem to understand why a schema is not already available inside the models themselves as you already type the models which you want to merge data with.

What i am asking is so simple and do-able with nothing else but extending the basic model to accept a new array key foreign_key as it is with primary_key but an array.

While MySQL already provide the relation inside the schema if it is set you can get it from there there is no need to generate an entire schema table

This functionality must be implemented somehow very easy just like a LEFT JOIN is used, but instead the actual keys you use variables for the keys as they can be many

[MGatner]

I know that it seems simple, but this issue balloons out of control very quickly. If you think you can solve it cleanly then you should be able to write your own App\Models\BaseModel and extend it to your other models and never need the framework to address it.

Tatter\Relations uses Schemas so the developer does not need to specify every relation and type, but there are other solutions that take that approach instead. Again, I recommend the forums if you'd like to peruse other people's solutions.

[crustamet]

You will need a mastermind for this man, i don't know how t o write it cleanly, i have tried a bunch of methods i just don't have the brain to do this kind of stuff, but for sure it can be done, as it for now I will use the grouping and linking in all my models done manually, i am just saying if there is somebody that knows how to solve this BEST feature of all ORM's please maybe we can all put some money on the line, maybe if we can spare some 50$ each to give it to some one that knows how to do this well ?

I am just saying, sorry to criticize your work, i know you did your best for the relations.