Is Lazy Loading from a model using another database possible?

[Ernst]

Im a beginner of CakePHP and I have run into a problem.

I have two tables that resides in different databases on the same SQL Server. I can load data from them respetively but not via associations.

Basically I have a Locations table and a Customers table. The Locations belongsTo Customers and Customers hasMany Locations.

When I have a list of all Locations I load the customer_id, address, zip_code, city and country_code, But instead of getting the customer_id from the Locations table I would like to get the name and nr (name of the customer code/number in the Customer table). Another thing to mention is that the table name for the customers table is not following naming conventions (is called tblCustomer).

Im using Jerremy Harris LazyLoad plugin but getting an error:

SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name ‘tblCustomer’.

My code:

src\Model\Table\LocationsTable.php:

<?php
// src/Model/Table/LocationsTable.php
namespace App\Model\Table;

use Cake\ORM\Table;

class LocationsTable extends Table {
    public function initialize(array $config): void {
        $this->belongsTo('Customers', [
            foreignKey' => 'customer_id'
        ]);
        $this->addBehavior('Timestamp');
    }
}

src\Model\Table\CustomersTable.php:

<?php
// src/Model/Table/CustomersTable.php
namespace App\Model\Table;
use Cake\ORM\Table;

class CustomersTable extends Table {
    public static function defaultConnectionName(): string {
        return 'db2';
    }
    public function initialize(array $config): void {
        $this->setTable('tblCustomer');
        $this->hasMany('Locations');
        $this->addBehavior('Timestamp');
    }
}

src\Model\Entity\Location.php:

<?php
// src/Model/Entity/Location.php
namespace App\Model\Entity;

use Cake\ORM\Entity;
use JeremyHarris\LazyLoad\ORM\LazyLoadEntityTrait;

class Location extends Entity {
    use LazyLoadEntityTrait;
    protected $_accessible = [
        '*' => true,
        'id' => false,
    ];
}

src\Controller\LocationsController.php:

<?php
// src/Controller/LocationsController.php

namespace App\Controller;

class LocationsController extends AppController {
    public function index() {
        $locations = $this->Locations->find('all');
        $this->set(compact('locations'));
     }
}

templates\Locations\index.php:

<!-- File: templates/Locations/index.php -->

<h1>Locations</h1>
<table>
    <tr>
        <th>Id</th>
        <th>Customer</th>
        <th>Address</th>
        <th>City</th>
        <th>Created</th>
    </tr>
    <?php foreach ($locations as $location): ?>
    <tr>
        <td>
            <?= $this->Html->link($location->id, ['action' => 'view', $location->id]) ?>
        </td>
        <td>
            <?= $location->customer->nr ?> - <?= $location->customer->name ?>
        </td>
        <td>
            <?= $location->address_1 ?>
        </td>
        <td>
            <?= $location->city ?>
        </td>
        <td>
            <?= $location->created->format(DATE_RFC850) ?>
        </td>
    </tr>
    <?php endforeach; ?>
</table>

Is it that its not possible to use Lazy Load between databases or is it the unconvetional table name that makes things difficult?

[Zuluru]

To confirm, you have a separate database configuration called db2 which has the connection details for this second database?

Have you tried reading directly from the Customers table? (e.g. (TableRegistry::get('Customers')->find()->...)

[Ernst]

Yes, thats correct.

I have not tried that particular code, in which file do I put that? However, I have a CustomersController.php and a view for Customers so I know that works as intended.

[Ernst]

I have corrected the markup so my code is more readable in the first post now. It was in the middle of the night when it was originally posted…

Also, it looks from the SQL Server error message that it tries to SELECT from the tblCustomer table in db1 although it is in db2. Am I correct?

[Ernst]

@Zuluru have you had the time to see my recent posts?

I was also not sure where to put this code you mentioned:

( TableRegistry::get('Customers')->find()->... )

[dreamingmind]

It looks like @Zuluru was just suggesting you run a straight Customer query to try and verify that table was working in isolation. The fragment he gave uses TableRegistry, a static class, so you should be able to use it in any class that is convenient for you… whatever controller you are working in.

[Ernst]

I have this controller and template for the customers model (tblCustomer table) and its working fine. However, not associated. I have a third page “Networks” whis is a table in the same db as location and they also got associations and they work fine.

The controller:

<?php
// src/Controller/CustomersController.php
namespace App\Controller;
use Cake\Datasource\ConnectionManager; // This line is required
    
class CustomersController extends AppController {
    public function index() {
        $this->loadComponent('Paginator');
        $customers = $this->Paginator->paginate($this->Customers->find());
        $this->set(compact('customers'));
    }
}

And the template:

<!-- File: templates/Customers/index.php -->

<h1>Customers</h1>
<table>
    <tr>
        <th>Cust. No</th>
        <th>Name</th>
        <th>City</th>
        <th>Modified</th>
    </tr>

    <!-- Here is where we iterate through our $customers query object, printing out article info -->

    <?php foreach ($customers as $customer): ?>
    <tr>
            <td>
                <?= $customer->Nr ?>
            </td>
            <td>
                <?= $customer->Name ?>
            </td>
            <td>
                <?= $customer->City ?>
            </td>
            <td>
                <?= $customer->Modified->format(DATE_RFC850) ?>
            </td>
        </tr>
       <?php endforeach; ?>
</table>

I updated my locations controller with the following code, but I cant get it to work in my template. Get the error message that customer is undefined. What am I doing wrong?

<?php
// src/Controller/LocationsController.php

namespace App\Controller;
use \Cake\ORM\TableRegistry;

class LocationsController extends AppController {
    public function index() {
		$locations = $this->Locations->find('all');
		$customers = (TableRegistry::getTableLocator()->get('Customers')->find());
		$this->set(compact('locations'));
    }
}

[Zuluru]

What’s the exact error message, and which line in the code you’ve shown does it occur on?

[Ernst]

Got the TableRegistry code to run now in file src/Controller/LocationsController.php. Just doing an echo of $customers in the file templates/Customers/index.php and getting this. no data:

SELECT Customers.Id AS Customers__Id, Customers.Status AS Customers__Status, Customers.Modified AS Customers__Modified, Customers.Category AS Customers__Category, Customers.Nr AS Customers__Nr, Customers.Name AS Customers__Name, Customers.Address1 AS Customers__Address1, Customers.Address2 AS Customers__Address2, Customers.PostalCode AS Customers__PostalCode, Customers.City AS Customers__City, Customers.Country AS Customers__Country, Customers.Phone AS Customers__Phone, Customers.Fax AS Customers__Fax, Customers.Custom1 AS Customers__Custom1, Customers.Custom2 AS Customers__Custom2, Customers.Custom3 AS Customers__Custom3, Customers.Custom4 AS Customers__Custom4, Customers.PriceListNr AS Customers__PriceListNr, Customers.Note AS Customers__Note, Customers.State AS Customers__State, Customers.Email AS Customers__Email FROM tblCustomer

[Zuluru]

From your posted code, $customers appears to be a query object, not the actual results. Output $customers->toArray()?

[Ernst]

Im feeling stupid right now.

Now I got the following in my controller:

<?php
// src/Controller/LocationsController.php

namespace App\Controller;
use \Cake\ORM\TableRegistry;

class LocationsController extends AppController {
    public function index() {
		$locations = $this->Locations->find('all');
		$customersQuery = (TableRegistry::getTableLocator()->get('Customers')->find()
			->where(['Customers.id =' => '20219'])			
		);
		$customers = $customersQuery->toArray();
		$this->set(compact('locations'));
		$this->set(compact('customers'));
    }
}

And the following in my template index.php:

<!-- File: templates/Locations/index.php -->

<h1>Locations</h1>

<?php print_r $customers; ?>

<table>
    <tr>
        <th>Id</th>
        <th>Customer</th>
		<th>Address</th>
		<th>City</th>
		<th>Created</th>
    </tr>

    <!-- Here is where we iterate through our $locations query object, printing out article info -->

    <?php foreach ($locations as $location): ?>
    <tr>
        <td>
            <?= $this->Html->link($location->id, ['action' => 'view', $location->id]) ?>
        </td>
		<td>
            <?= $location->customer_id ?>
        </td>
		<td>
            <?= $location->address_1 ?>
        </td>
		<td>
            <?= $location->city ?>
        </td>
        <td>
            <?= $location->created->format(DATE_RFC850) ?>
        </td>
    </tr>
    <?php endforeach; ?>
</table>

Im getting the following error:

syntax error, unexpected ‘$customers’ (T_VARIABLE)

ParseError

Error in: ROOT\templates\Locations\index.php, line 5

[dreamingmind]

print_r is a function. missing parenthesis:

//index.php line 5

<?php print_r($customers); ?>

[Ernst]

Yes it is working now, I can make a query from the Customers table via TableRegistry::getTableLocator() from the Locations controller. But can we get it to work with Lazy Loading?

I need to collect name and customer_number from the Customers table for each row in the Locations table where locations.customer_id = customers.id.

I would like to use something like:

<?= $location->customer->name; ?>

[Ernst]

Is it okay to Bump this post. Im still stuck…

[dreamingmind]

Lazy loading? hmmm

@Ernst can you explain what you’re expecting when you say “get it to work with Lazy Loading”?

According to my cobbled-together sense of the term it would mean ‘always configured, but only instantiated when and if needed’.

In your example, the Table is configured in

TableRegistry::getTableLocator()->get('Customers')

and used right away to make a query.

Probably Associations?

I am going to guess from other clues in your code that you are looking to get the Customers as an association to Locations.

Your query should be something like this:

$locations = $this->Locations->find('all')->contain('Customers);

But you will have to have the Association defined in your Locations table. If you baked your Locations Controller/Model/templates from the console (bin/cake bake all Locations), and you followed Cake naming conventions, then everything should be ready to go. If not you will need to write the Association on Locations.

[Ernst]

@dreamingmind first of all, the Customers model/controller/view and the Locations model/controller/view is working fine on their own.

I am using this line in my LocationsController.php:

$locations = $this->Locations->find('all')->contain('Customers);

This is my LocationsTable.php:

<?php
// src/Model/Table/LocationsTable.php
namespace App\Model\Table;

use Cake\ORM\Table;

class LocationsTable extends Table {
    public function initialize(array $config): void
    {
		$this->belongsTo('Customers', [
			'foreignKey' => 'customer_id',
			'bindingKey' => 'id'
		]);
		$this->hasMany('Networks', [
			'foreignKey' => 'location_id',
			'bindingKey' => 'id'
		]);
        $this->addBehavior('Timestamp');
    }
}

This is my CustomersTable.php:

<?php
// src/Model/Table/CustomersTable.php
namespace App\Model\Table;
use Cake\ORM\Table;

class CustomersTable extends Table {
	public static function defaultConnectionName(): string {
		return 'db2';
	}
	public function initialize(array $config): void {
		$this->setTable('tblCustomer');
		$this->hasMany('Locations', [
			'foreignKey' => 'customer_id',
			'bindingKey' => 'id'
		]);
        $this->addBehavior('Timestamp');
    }
}

Im getting the following error which seems to me that the LocationsController tries to find the table with the customer data (tblCustomer) which is not present in the db1 database, only in db2:

SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name ‘tblCustomer’.

Should this work in CakePHP or do I need to use Lazy Loading instead of Eager Loading? I just want it to work even if the tables for customer data and location data is in different databases. Or is it the fact that the customer table is named tblCustomer instead of just customers thats causing the problems? I can not change this. Nor can I have the customer data in the same database as the rest of the application.

Any workarounds? Am I making any sense in describing the case and problem?

[Zuluru]

I believe that the code here is problematic because for a belongsTo relation it will by default do a join between the tables and load all the data in a single query, but it’s not able to join across two connections. Per the documentation, you should be able to use 'strategy' => 'select' when defining that association to override this and have it issue two separate queries. Whether those queries will execute correctly on separate connections is something I don’t know.