Containing Polymorphic Associations

Need Help
1

I’m developing a scheduling system and I’m using polymorphic associations to do it. I have several models that can be scheduled, but for now I’m focusing on two: Tasks and Vacations.

I have my Schedule model set up so it has a foreign_id and a class column. The idea being, a Schedule can belong to a Task, or a Vacation, based on the value of the class column:

// In SchedulesTable.php
$this->belongsTo('Tasks', [
    'className' => 'Tasks',
    'foreignKey' => 'foreign_id',
    'conditions' => [
        'Schedules.class' => 'Tasks',
    ],
]);

$this->belongsTo('Vacations', [
    'className' => 'Vacations',
    'foreignKey' => 'foreign_id',
    'conditions' => [
        'Schedules.class' => 'Vacations',
    ],
]);

// In TasksTable.php
$this->hasMany('Schedules', [
    'className' => 'Schedules',
    'foreignKey' => 'foreign_id',
    'conditions' => [
        'Schedules.class' => 'Tasks'
    ]
]);

// In VacationsTable.php
$this->hasMany('Schedules', [
    'className' => 'Schedules',
    'foreignKey' => 'foreign_id',
    'conditions' => [
        'Schedules.class' => 'Vacations'
    ]
]);

My issue occurs when I try to find Schedules, and contain both Tasks and Vacations, because when I do so, I get no results. When I change the joinType of the associations to “LEFT”, it works, but I can’t do any deeper containing (e.g. I can’t contain Tasks.Jobs, or Tasks.Users) because the Vacations model isn’t related to either Jobs or Users.

The only way I can see of getting it to work is to convert all the associations to “LEFT”, but that doesn’t strike me as a solution.

Am I going about this the right way, or is there a better solution?

2

Maybe you can try to do something with:

http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#loading-additional-associations

Probably you are querying first Schedules to get data between two dates, after that you can filter vacations from schedules using class columns and Collections. Finally you could use loadInto to do the contain without issues.

Or try something with lazy loading: http://book.cakephp.org/3.0/en/orm/entities.html#lazy-load-associations

3

I wonder if you can go into a little more detail based on my code, as I’m doing things slightly differently to how you have guessed.

The code is for a calendar view, with a row for each user, and a column for each day (e.g. Mon - Sun). Thus, my query is initially for the Users, and then I’m containing the Schedules. The end result is an array of Users, each with an array of Schedules which belong to them (Schedules belongsTo Users).

$users = $this->Schedules->Users->find('all')
->contain([
    'Schedules' => function ($q) use ($start_day, $end_day) {
        return $q->where([
            'start >=' => $start_day,
            'end <=' => $end_day,
        ])->order(['start' => 'ASC']);
    },
    'Schedules.Tasks',
    'Schedules.Tasks.Jobs',
    'Schedules.Vacations',
])->order(['Users' => 'ASC']);

If I were to do the additional loading, would I have to iterate over each user, filter their schedules, and then load the additional associations? Or is there a way which does that all in one go?

4

First, you can also do single table inheritance with this plugin https://github.com/UseMuffin/Sti maybe could be easier.

Following you example, I will do something like that (probably the code will have some errors):

$users = $this->Schedules->Users->find('all')
->contain([
    'Schedules' => function ($q) use ($start_day, $end_day) {
        return $q->where([
            'start >=' => $start_day,
            'end <=' => $end_day,
        ])->order(['start' => 'ASC']);
    }
])->order(['Users' => 'ASC'])
->all();

$users = $users->map(function ($user, $key) {
     $user->schedules = collection($user->schedules)->map(function($schedule, $key2) {
         if ($schedule->class == 'Tasks') {
             return $this->Tasks->loadInto($schedule,  ['Tasks', 'Tasks.Jobs']);
         } else {
                 return $this->Vacations->loadInto($schedule,  ['Vacations']);
         }
     })->toArray();
     return $user;
})->toArray();

You can change calling map() with foreach, if you prefer. All the process could be slow, because you are going to do a lot of queries for each user - schedules. So you should test if it fits your needs.

You could refactor and improve performance later. Maybe you could filter the schedule in two groups: tasks and vacations and call loadInto one/two times for each user. Or you can also change your query, so you get the all the schedules between two dates, then do the loadInto trick and then use $collection->groupBy() to group schedules by each user.
http://book.cakephp.org/3.0/en/core-libraries/collections.html#Cake\Collection\Collection::groupBy