How to join a distant table with one intermediate table

[Naghal]

Given the Schema Files -> FilesPaymentAgreements -> FilesPaymentAgreementsDetail, where Files is the master table, is it possible to add define a join for a field on table FilesPaymentAgreementsDetail?

I have tried these two things:

$joins = [
    'FilesPaymentAgreementsDetail.PaymentDate' => [
        'from_table'      => 'FilesPaymentAgreements',
        'from_col'        => 'ID',
        'to_table'        => 'FilesPaymentAgreementsDetail',
        'to_col'          => 'ExID',
        'to_value_column' => 'PaymentDate',
        'to_clause' => function($q) {
            $q->join('FilesPaymentAgreementsDetail', 'FilesPaymentAgreementsDetail.ExID', '=', 'FilesPaymentAgreements.ID');
        },
    ],
];

$qbp = new \timgws\JoinSupportingQueryBuilderParser(['FilesPaymentAgreementsDetail.PaymentDate'], $joins); 
dd($qbp->parse(request()->query_builder, DB::table('Files')->toSql());

Gave me: select * from [Files] where exists (select 1 from [FilesPaymentAgreements] where FilesPaymentAgreements.ExID = Files.ID and [Inactive] = ?) and exists (select 1 from [FilesPaymentAgreements] where FilesPaymentAgreements.ExID = Files.ID and [PaymentMethodID] = ?)

$joins = [
    'FilesPaymentAgreementsDetail.PaymentDate' => [
        [
        'from_table'      => 'Files',
        'from_col'        => 'ID',
        'to_table'        => 'FilesPaymentAgreements',
        'to_col'          => 'ExID',
        'to_value_column' => 'PaymentDate',
        ],
        [
            'from_table'      => 'FilesPaymentAgreements',
            'from_col'        => 'ID',
            'to_table'        => 'FilesPaymentAgreementsDetail',
            'to_col'          => 'ExID',
            'to_value_column' => 'PaymentDate',
        ],
    ],
];

Gave me: "Undefined array key \"to_table\""

[Naghal]

I have experimented quickly with the code, at first sight, it seems like we could add the following to the buildSubclauseQuery method at line 115.

if (array_key_exists('additionnal_join', $subclause) && is_array($subclause['additionnal_join'])) {
    foreach($subclause['additionnal_join'] as $join) {
        $q->join($join['to_table'], $join['to_col'], '=', $join['from_col']);
    }
}

And use it like so:

$joins = [
    'FilesPaymentAgreementsDetail.PaymentDate' => [
        'from_table'      => 'Files',
        'from_col'        => 'ID',
        'to_table'        => 'FilesPaymentAgreementsDetail',
        'to_col'          => 'ExID',
        'to_value_column' => 'PaymentDate',
        'additionnal_join' => [
            [
                'from_col'        => 'FilesPaymentAgreementsDetail.ExID',
                'to_table'        => 'FilesPaymentAgreements',
                'to_col'          => 'FilesPaymentAgreements.ID',
            ],
            [
                'from_col'        => 'FilesPaymentAgreements.ExID',
                'to_table'        => 'Files',
                'to_col'          => 'Files.ID',
            ],
        ]
    ],
];

Which gives the following sql:

select 
  * 
from 
  [Files] 
where 
  exists (
    select 
      1 
    from 
      [FilesPaymentAgreementsDetail] 
      inner join [FilesPaymentAgreements] on [FilesPaymentAgreements].[ID] = [FilesPaymentAgreementsDetail].[ExID] 
      inner join [Files] on [Files].[ID] = [FilesPaymentAgreements].[ExID] 
    where 
      FilesPaymentAgreementsDetail.ExID = Files.ID 
      and [PaymentDate] = ?
  )