Take a new look at `addRelatedTable` method

[Martin-4Spaces]

Take following model

class CategoryModel extends Model {

    public $hasOne = [
        'parent' => [
            'class' => CategoryModel::class,
            'otherField' => 'child',
            'joinSelfAs' => 'id',
            'joinOtherAs' => 'parent_id',
        ],
    ];

    public $hasMany = [
        'child' => [
            'class' => CategoryModel::class,
            'otherField' => 'parent',
            'joinSelfAs' => 'parent_id',
            'joinOtherAs' => 'id',
        ],
    ];

}

Populate data such that category 1 has multiple children. And run this code

$item = new Category();
$item->find(1);
$item->children->find();
echo Database::connect()->showLastQuery();

We see this query

SELECT `categories`.* 
FROM `categories` 
LEFT OUTER JOIN `categories` `parents_categories` ON `parents_categories`.`id` = `categories`.`parent_id`
WHERE `parents_categories`.`parent_id` = 1

Which is wrong.

If we however take a look at the user/roles example. One user has multiple roles. $user->roles->find() will work fine. Even with customised table names, join tables and field name. But when handling simple parent/child relations inside the same table, it no longer works.

We can however work around it like this

$item = new Category();
$item->find(1);
$item->children = (new CategoryModel())
    ->where('parent_id', $item->id)
    ->find();

But the same error happens when we are trying to use the parent relation

$item = new Category();
$item->find(1);
$item->children = (new CategoryModel())
    ->whereRelated('parent', 'id', $item->id)
    ->find();

Taking a look at this scenario

$item = (new ProductCategoryModel())
        ->includeRelated('child')
        ->where('id', 1)
        ->find();
echo Database::connect()->showLastQuery();

We see this query

SELECT `categories`.*, `children_categories`.`id` AS `children_id`, `children_categories`.`parent_id` AS `children_parent_id`
FROM `categories` 
LEFT OUTER JOIN `categories` `children_categories` ON `children_categories`.`id` = `categories`.`parent_id`
WHERE `categories`.`id` = 1

Which is also wrong.

I would like in the near future to take a new look at how relations should be handled.

We need to cover all following use cases

• Relations in the same table without join table
• Relations in the same table with join table
• Relations in different tables with join table
• All above with and without custom naming
• All above as one-to-one, one-to-many and many-to-many

And we need to test that all following methods works

• includeRelated both ways
• whereRelated both ways
• __get relation on entity