Accessing data in another table without a relationship in a query

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('Users', [
    'setForeignKey' => 'user_id',
    'joinType => 'INNER'
]);

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

$this->belongsTo('Accounts', [
    'setForeignKey' => 'account_id'
]);

My Account Leads Component

$query = $LeadArchives->find('myAccountLeads', [
    'contain' => ['Users', 'Contacts', 'Accounts'],
    'status' => $this->status,
    'user_id' => $this->params[9],
    'account_id' => $this->params[0]
]);

Lead Archives Table - Finder

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

public function findMyAccountLeads(Query $query, array $options): object
{

    $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 WLL THROW EXCEPTION
            ]
        ]);

    return $query;
}

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 in the belongsTo.

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

What I've tried

After checking the cookbook I found unions here 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.

My Account Leads Component

$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, maybe an expression in the OR section of the first query outlined.

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.

As documented here I can use 'foreignKey' instead of ->setForeignKey.

If I use ->setForeignKey it will invoke the frameworks conventions which means that if I want to associate with the contact archives table the belongsTo must be declared like:

$this->belongsTo('ContactArchives', [
    'setForeignKey' => 'contact_archive_id'
]);

But I only have contact_id in the contact archives table which means I must bypass the conventions and use the below to access the active and archives contacts table:

Active Contacts Table

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

Archive Contacts Table

$this->belongsTo('ContactArchives', [
    'foreignKey' => 'contact_id'
]);