Created by Nathan, 24th Sep 2012. (originally Lighthouse ticket #3229):

What I did

My setup:
I have two separate databases working together to provide data, both of them are postgres 8.x. I have been using cake as any easy way to view data from both. In database #1, I have accounts (Account). In database #2 I have uploaded files (SharedConnectFile) and a join table (account_file_mapping) mapping Account to SharedConnectFile.

I set up a HABTM relationship in Account and am using the 'with' statement to communicate between databases.

public $hasAndBelongsToMany = array(
    'SharedConnectFile' => array(
        'className' => 'ConnectFile',
        'joinTable' => 'account_files_mapping',
        'foreignKey' => 'fk_accounts_id',
        'associationForeignKey' => 'fk_files_id',
        'order' => 'SharedConnectFile.id DESC',
        'with' => 'AccountFileMapping'
    )
);

When I attempt to get the model data of SharedConnectFile to display in Account, I get any empty array back even though the query says that 9 rows were returned.
I tracked the issue down to this file: lib\Cake\Model\Datasource\DboSource.php, public function queryAssociation($model, &$linkModel, $type, $association, $assocData, &$queryData, $external = false, &$resultSet, $recursive, $stack), around line 1241: if ($type === 'hasAndBelongsToMany')

Around line 1245 there is a foreach loop checking to see if data should be merged into the resultSet based on 'with' being defined and a type value compare.
if (isset($data[$with]) && $data[$with][$foreignKey] === $row[$modelAlias][$modelPK])

For me this is where my data goes missing. The === compare fails because $data[$with][$foreignKey] is for some reason a string and $row[$modelAlias][$modelPK]) an integer.

What happened

When the following query is run when loading a Account view, I see that there are 9 rows affected and returned of SharedConnectFile. However, the data never makes it back up to the view. DebugKit gives:
result

Account
Center
User
ConnectFile
SharedConnectFile(empty)

4   SELECT "SharedConnectFile"."id" AS "SharedConnectFile__id", "SharedConnectFile"."filename" AS "SharedConnectFile__filename", "SharedConnectFile"."content_type" AS "SharedConnectFile__content_type", "SharedConnectFile"."size" AS "SharedConnectFile__size", "SharedConnectFile"."is_published" AS "SharedConnectFile__is_published", "SharedConnectFile"."thumbnail" AS "SharedConnectFile__thumbnail", "SharedConnectFile"."width" AS "SharedConnectFile__width", "SharedConnectFile"."height" AS "SharedConnectFile__height", "SharedConnectFile"."date_created" AS "SharedConnectFile__date_created", "SharedConnectFile"."date_modified" AS "SharedConnectFile__date_modified", "SharedConnectFile"."expires_on" AS "SharedConnectFile__expires_on", "SharedConnectFile"."is_verified" AS "SharedConnectFile__is_verified", "SharedConnectFile"."caption" AS "SharedConnectFile__caption", "SharedConnectFile"."is_deleted" AS "SharedConnectFile__is_deleted", "SharedConnectFile"."fk_account_id" AS "SharedConnectFile__fk_account_id", "SharedConnectFile"."fk_user_id" AS "SharedConnectFile__fk_user_id", "AccountFileMapping"."id" AS "AccountFileMapping__id", "AccountFileMapping"."fk_accounts_id" AS "AccountFileMapping__fk_accounts_id", "AccountFileMapping"."fk_files_id" AS "AccountFileMapping__fk_files_id", "AccountFileMapping"."fk_company_id" AS "AccountFileMapping__fk_company_id" 
FROM "connect_files" AS "SharedConnectFile" 
JOIN "account_files_mapping" AS "AccountFileMapping" ON ("AccountFileMapping"."fk_accounts_id" = xxx AND "AccountFileMapping"."fk_files_id" = "SharedConnectFile"."id") 
ORDER BY "SharedConnectFile"."id" DESC      9   9   566

What I expected to happen

I am expecting the results from the SharedConnectFile query to be available in my view and not being tossed away.