Tatter\Relations - Feature: Loading additional fields in join tables

Hey all,

I'm currently fooling around with the \Tatter\Relations plugin and I'm missing one function.

I have two tables 'matches' and 'players' wich have a manyToMany relationship via the table 'matches_players'.
This join table holds an additional field besides the indexes, which is called 'available' and keeps the availability of each player to participate in each respective match.

As far as I see, \Tatter\Relations has no option to include such fields in the result yet (please correct me if I'm wrong ).

I added a short workaround for that in the BaseTrait which goes as follows:

PHP Code:

// Get pivot table from schema
$pivotTable = $this->_schema()->tables->{reset($relation->pivots)[2]};

// Get addional pivot table fields
$pivotTableFields = array_diff(array_keys((array) $pivotTable->fields), [reset($relation->pivots)[3], next($relation->pivots)[1]]);

// Add pivot table fields to select
if (!empty($pivotTableFields))
{
    $builder->select(preg_filter('/^/', $pivotTable->name . '.', $pivotTableFields));
} 


See: https://github.com/sfadschm/codeigniter4...#L149-L159

This automatically adds all fields in the join table except the pivot fields to the select list for manyToMany relations.
While this is not really pretty yet, I wonder if this functionality is useful for others to (and desired).
If so, I'd like to implement this fully and maybe bind it to a config setting or even a new modifier function (like $this->withAdditionalFields(true).

Would be great to get some opinions on this.
Maybe from @MGatner?

I'll have to think on this. Relations strives to be stable across all the database drivers by locking in some "standards", which already creates quite a bit of work. I could see how these additional pivot table fields could be crucial however, and maybe even desirable for filtering ("get matches with players where available").

Does anyone have information on how ORMs typically handle this? I can do some research later but it probably will have to wait until after CodeIgniter 4.1 release, 11/30.

Thats exactly one of my use-cases. I see the issue with cross-database handling though.
Unfortunately, I have no idea about databases other than MySQL. If I can be of any help later on, let me know.

Looking forward to 4.1