HasMany's JOIN ON field LIKE OtherTable.field

Need Help
1

Dear CakePHP’s Community,
I’m working with a legacy DB, and I’m struggling to make an HasMany association that will use a LIKE condition on VARCHAR fields instead of the usual id field.
Is that even possibile?

I even tried to use a ->join() with the Query builder, and it works, but doesn’t seem to let me specify the strategy (I would like to use a “select” strategy, so that it will generate a subquery).

Thanks,

Paolo

2

CakePHP version?
Can you share the code and table with PF, and FK

3

CakePHP version is 3.1.4, but I can update if that can solve.

Let’s say we have those two tables:

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  province VARCHAR(24) NOT NULL
);

CREATE TABLE provinces (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(24) NOT NULL
);

I tried a

$accounts = $this->Accounts->find()->join([
‘table’ => ‘provinces’,
‘alias’ => ‘p’,
‘type’ => ‘LEFT’,
‘conditions’ => ‘p.name LIKE Accounts.province’,
]);
and it works, but I would like to specify ‘strategy’ => ‘select’, because in the result object I would like to see another object containing the associated provinces.

A better solution would be if I can specify in the AccountsTable a JOIN on a LIKE. I imagine an hypothetical ‘bindingMethod’ property.

$this->hasMany(‘Provinces’, [
‘foreignKey’ => ‘name’,
‘bindingKey’ => ‘province’,
‘bindingMethong’ => ‘like’,
‘strategy’ => ‘select’,
]);

Thanks for your help,

Paolo

4

What I understand, You wan to JOIN provinces and accounts where
Table accounts belongsTo provinces
and
Table provinces hasMany accounts

What kind of value saved in accounts.province?

CREATE TABLE accounts (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      province VARCHAR(24) NOT NULL
    );

CREATE TABLE provinces (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(24) NOT NULL
);

Here code Cakephp 3 and same like I do in Cakephp 2.

//Table/AccountsTable.php

public function initialize(array $config)
 {
        $this->belongsTo('Provinces', [
            'className' => 'Provinces',
            'foreignKey' => 'province',
            'joinType' => 'INNER'
        ]);
}

//Table/ProvincesTable.php

public function initialize(array $config)
 {
        $this->hasMany('Accounts', [
            'className' => 'Accounts',
            'foreignKey' => 'province',
        ]);
}
5

The only problem is that I need the JOIN made with a “LIKE” operator, because in the Accounts table I have
1, Microsoft, Seattle
2, Apple, CUPERTINO

while in the Provinces table I have
1, SEATTLE
2, Cupertino

And with your method they won’t match because of the different uppercases.

Thanks,

P.

6

I just waiting your answer about data saved in Accounts.provinces.
Maybe you can try do like this.

//Table/AccountsTable.php

public function initialize(array $config)
 {
        $this->belongsTo('Provinces', [
            'className' => 'Provinces',
            'foreignKey' => 'province',
            'conditions' => ['Provinces.name LIKE Accounts.province'],              
            'joinType' => 'INNER'
        ]);
}

OR you can test like this for conditions

'conditions' => ['Provinces.name LIKE %Accounts.province%'],