Associating 2 similar models from different databases to a same model

Question

I'm developing an inventory system with CakePHP 3.3.16, using 2 databases. I was able to connect both databes and show contents from both. In the first, I have 2 tables:

Assets (id, serial_number, is_connected)

AssetsAssignations (id, name, asset_id, last_inventory_date)

In the second database, I have a table

Machines (id, serial_number, last_connected_date)

which represents a sub-set of Assets connected to an ip address. So some serial numbers can be in both Assets and Machines.

in MachinesController index, I put this:

    $connection = ConnectionManager::get('db2'); // where my second database is configured 
    $machines = $connection->execute('SELECT * FROM MACHINE');

    $this->set(compact('machines'));
    $this->set('_serialize', ['machines']);

In AssetController, I put this:

    $assets = $this->Assets->find();
    $this->set(compact('assets'));
    $this->set('_serialize', ['assets']);

In AssetsAssignationsController, I have this for the moment:

    $query = $this->AssetsAssignations->find()
        ->contain(['Assets']);
    $filter = $this->Filter->prg($query);
    $assetsAssignations = $this->paginate($filter, ['limit' => 50]);
    
    $connection = ConnectionManager::get('db2'); // 'kace_db' where my second database is configured 
    $machines = $connection->execute('SELECT * FROM MACHINE ORDER BY NAME ASC');

    $this->set(compact('machines'));
    $this->set('_serialize', ['machines']);

    $this->set(compact('assetsAssignations', 'machines'));
    $this->set('_serialize', ['assetsAssignations']);

What I need in AssetsAssignations index.ctp is to show the asset_id, serial_number from Assets. And if we have the same serial_number in the other database (in table Machines), we show the ip_address and last_inventory_date for the same entry.

So, it like I'm trying to associate 2 similar tables with a 3rd one based on the same column (serial_number).

Here is AssetsAssignations index.ctp:

        <?php foreach ($assetsAssignations as $assetsAssignation): ?>
            <tr>
                <td><?= h($assetsAssignation->asset->serial_number) ?></td>
                <td><?= h($assetsAssignation->asset->is_connected) ?></td>

and then if the same serial_number exists in Machines, I want to show it in the same view:

<td><?= h($assetsAssignation->something->last_conneced_date) ?></td>

My question : Is this possible ? and how to do it ?

I appreciate your help.

Please alwas mention your _exact_ CakePHP version (last line in `vendor/cakephp/cakephp/VERSION.txt` or `lib/Cake/VERSION.txt`) - thanks! MySQL supports cross database joins, so you _might_ be able to simply go for that and create an association between `Assets` and `Machines` based on the serial number **https://stackoverflow.com/questions/32033558/how-to-use-different-datasources-in-a-query-using-cakephp3**. — ndm, Oct 11 '17 at 17:54

score 0 · Accepted Answer · answered Jun 08 '18 at 14:24

I found a solution. in my index.ctp:

<?php 
                        $ip_address = $assetsAssignation->ip_address;
                        $mac_address = $assetsAssignation->mac_address;

                        $is_found = False;
                        foreach ($machines as $row) : 
                            if ($row['SERIAL_NUMBER'] == $assetsAssignation->asset->serial_number):
                                $ip_address = $row['IP'];                                
                                $mac_address = $row['MAC'];
                                $is_found = True;
                            endif;                                
                        endforeach; ?>
                <?php if($is_found): ?>
                    <td><?= h($ip_address) ?></td>
                    <td><?= h($mac_address) ?></td>
                <?php else: ?>
                    <td><span style="color:#fc352d;text-align:center;"><?= h($ip_address) ?></span></td>                
                    <td><span style="color:#fc352d;text-align:center;"><?= h($mac_address) ?></span></td>
                <?php endif ?>

Associating 2 similar models from different databases to a same model

1 Answers1