CakePHP Version 4.4.11

Hi,

Introduction

In text I would like to do the following:

1. visit the lead archives table and select records with the account_id.
2. visit the contacts table and select records with the account_id.
3. visit the contact archives table and select records with the account_id.

Lead Archives Table

$this->belongsTo('Contacts', [
    'setForeignKey' => 'contact_id'
]);

Lead Archives Controller

Example to help explain what I’m trying to do.

$query
    ->where([
        'LeadArchives.status' => $options['status'],
        'LeadArchives.user_id' => $options['user_id'],
        'OR' => [
             ['LeadArchives.account_id' => $options['account_id']],
             ['Contacts.account_id' => $options['account_id']],
             ['ContactArchives.account_id' => $options['account_id']] <-- THIS WILL THROW EXCEPTION
        ]
     ]);

Obviously the exception will be thrown because the lead archives table only has a relationship
with the active contact table via its id which is contact_id.

If I wanted a relationship with the contact archives table the id would have to be contact_archive_id.

What I’ve tried

After checking the cookbook I found unions which enable me to access other tables without a relationship.

The following does visit the table with no relationship (ContactArchives) and retrieves the correct records but of course I’m not visiting the lead archives table.

$queryOne = $Contacts->find()
     ->where([
          'account_id' => 1998
      ]);

$queryTwo = $ContactArchives->find()
      ->where([
          'account_id' => 1998
      ]);

$query = $queryTwo->union($queryOne);

Again to help explain what I’m trying to do please see the following example.

$query = $LeadArchives->find()
    ->where([
        'account_id' => 1998
     ]);
       
$queryOne = $Contacts->find()
     ->where([
          'account_id' => 1998
      ]);

$queryTwo = $ContactArchives->find()
      ->where([
          'account_id' => 1998
      ]);

$query = $queryTwo->union($queryOne);

Question

Is there any way to pin that lead archives query to the union query or is there a better way to do it all together.

Also to note I’m not sure if theres anything in v5 that would help but I’m planning to do the upgrade in the next couple of months if there is.

Thanks, Zenzs.