Open Naghal opened 1 year ago
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] = ?
)
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:
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] = ?)
Gave me:
"Undefined array key \"to_table\""