Oct 26, 2023

Level 4

Complex Polymorphic tables, multiple siblings, how to best associate/retrieve the siblings?

I have several morphed models using a polymorphic table "positions":

$table->nullableMorphs('positionable') // positionable_type & positionable_id
$table->foreignId('role_id')->nullable();
$table->foreignId('person_id')->nullable();

I've success constructing relationships that retrieve the roles for a Project model or the people for a Project model. I have a trait "positionable" which defines the morphToMany relationship for people and roles.

return $this->morphToMany(Roles::class, 'positionable', 'positions');

$test = Project::with('roles')->get(); // this works fine, but I'd like to have the person noted for each role

I can join the other table in a custom bit of query, but this seems to require renaming columns so they can reside along side the with() table columns. This works, but returns an object with the "renamed columns and not a sub item" issues I'm trying to resolve.

    public function people()
    {
        return $this->morphToMany(Person::class, 'positionable', 'positions');
            ->join('roles', 'roles.id', '=', 'positions.role_id')
            ->addSelect(
                'people.*',
                'roles.id as role_id',
                'roles.name as role_name',
                'roles.memo as role_memo'
        );
    }

$test = Project::with('roles.person')->get(); This would be ideal, but I'm not seeing a way to place a "person" method in the Roles model which understands anything about the polymorphic criteria connecting the Project to a role and a person.

The same can be said for trying this in the other direction: $test = Project::with('people.role')->get();

Should I live with the renamed columns or is there an eloquent way to have person or role receive the necessary criteria?

    public function people()
    {
        return $this->belongsToMany(Person::class, 'positions');
    }

The above is what Roles knows about people. This would need a companion function 'person' which would somehow include criteria passed to it or I need to go about this in an entirely different way.

This is my first major application using Laravel, however I have several years of PHP/MySQL experience. I appreciate any suggestions or assistance.

LaryAI

2 years ago

Level 58

To associate and retrieve the siblings (roles and people) for a Project model, you can use a combination of relationships and custom queries. Here's a possible solution:

Define the relationships in your Project model:

public function roles()
{
    return $this->morphToMany(Role::class, 'positionable', 'positions');
}

public function people()
{
    return $this->morphToMany(Person::class, 'positionable', 'positions');
}

To retrieve the roles with the associated people, you can use a custom query with a join:

$test = Project::with(['roles', 'people'])
    ->join('positions', 'projects.id', '=', 'positions.positionable_id')
    ->join('roles', 'roles.id', '=', 'positions.role_id')
    ->join('people', 'people.id', '=', 'positions.person_id')
    ->get();

If you want to retrieve the people for each role, you can define a relationship in your Role model:

public function people()
{
    return $this->morphToMany(Person::class, 'positionable', 'positions');
}

Then, you can retrieve the roles with their associated people using:

$test = Project::with('roles.people')->get();

Similarly, if you want to retrieve the roles for each person, you can define a relationship in your Person model:

public function roles()
{
    return $this->morphToMany(Role::class, 'positionable', 'positions');
}

Then, you can retrieve the people with their associated roles using:

$test = Project::with('people.roles')->get();

Note: Make sure to replace Role and Person with the actual class names of your Role and Person models.

metroeast

2 years ago

Level 4

LaryAI,

When I try this approach for Project roles, each role has an empty "people" section in the returned object.

When I try this for Project people, each person has an empty "roles" section in the returned object.

A project may have many positions each can be any of several roles, a person may or may not be associated with a position record. Allowing for an "open" position for a project.

Each position record will have a morph to model, which is projects in this example. Projects is what I'm calling the parent model in this case, and each parent model through "positions" can have many roles and people. But each position record is a single role and a single person.

Tray2

2 years ago

Level 73

@metroeast Complex queries with eloquent, or the query builder is always a headache. If you have a working SQL then I suggest pushing it down into the database as a view.

Here is a guide on how to do juast that

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

1 like

metroeast

2 years ago

Level 4

I think I need to re-evaluate my approach. I think I'm seeing that for Laravel, I should introduce another table to do this "as expected." I think I need to split the functions of the the table I'm calling "positions;" which is acting as the morph join as well as the parent for multiple children. The new tables would be "positionables" (following convention) which would continue to do the morph join with a "positions" table which would operate as a simple many to many record, connecting the position with a role and a person as children records.

I think retrieval would then look like this:

$test = Project::with([
    'positions' => [
        'person',
        'role',
    ],
])->get();

I'll give that a go first, but I appreciate the suggestions around moving to a view for complex joins and queries. It's something I need to explore more in general.

metroeast

2 years ago

Level 4

Okay… I'm pleased to report that diligence has lead me to understand that I had a poorly described schema plan, and now I have the correct relationships using the typical calls.

The correct relationship is morphMany; I had mistakenly been pursuing a morphToMany relationship.

Each morphed model has many positions. Each position has other parents, which define things like the role of the position, and the person record when the position is filled.

I'm glad I kept looking at why I was storing the information, and this works well with built-in relationships, queries and resulting objects.

Please or to participate in this conversation.