CakePHP3 Linking Table Together in Same Model (Inner Join)
<p>i've a table named 'customers' with following (shortened) schema:</p>

<pre><code>id | customer_id | type | salutation | forename | surname | created | modified
</code></pre>

<p>In this table, I want to store people, which are may be associated:</p>

<pre>
id | customer_id | type    | salutation | forename | surname | created             | modified
 1 | NULL        | husband | Mr.        | John     | Doe     | 2016-01-05 10:00:00 | 2016-01-05 10:00:00
 2 |  1          | wife    | Mrs.       | Jane     | Doe     | 2016-01-05 10:01:00 | 2016-01-05 10:01:00
 3 |  1          | child   | Mr.        | Jim      | Doe     | 2016-01-05 10:02:00 | 2016-01-05 10:02:00
</pre>

<p>Customers with "customer_id" = NULL are the master customers, but #2 and #3 refers to #1.</p>

<p>I've created the table in phpmyadmin and do:
"bin/cake bake all customers" without errors.
Then I created the 'master customer'. When I create the second account, I expect that the select-field shows customer #1, but the dropdown-field is empty.</p>

<p>The model:</p>

<pre><code>public function initialize(array $config)
{
    parent::initialize($config);

    $this-&gt;table('customers');
    $this-&gt;displayField('id');
    $this-&gt;primaryKey('id');

    $this-&gt;belongsTo('Customers', [
        'foreignKey' =&gt; 'customer_id',
        'joinType' =&gt; 'INNER'
    ]);
    $this-&gt;hasMany('Customers', [
        'foreignKey' =&gt; 'customer_id'
    ]);
}
</code></pre>

<p>If you need further information or more piece of code, let me know.<br>
Many thanks in advance. </p>

<p>best regards<br>
Martin</p>

<hr>

<p><strong>SQL to reproduce</strong></p>

<pre><code>CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL,
  `typ` varchar(10) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `salutation` varchar(4) NOT NULL,
  `prename` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `customers` (`id`, `typ`, `customer_id`, `salutation`, `prename`, `surname`, `created`, `modified`) VALUES
(1, 'husband', NULL, 'Mr.', 'John', 'Doe', '2016-03-02 21:26:32', '2016-03-02 21:26:32'),
(2, 'wife', 1, 'Ms.', 'Jane', 'Doe', '2016-03-02 21:27:25', '2016-03-02 22:10:05'),
(3, 'child', 1, 'Mr.', 'Jim', 'Doe', '2016-03-02 21:27:41', '2016-03-02 22:10:15');

ALTER TABLE `customers` ADD PRIMARY KEY (`id`), ADD KEY `customers_fk0` (`customer_id`);
ALTER TABLE `customers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
ALTER TABLE `customers` ADD CONSTRAINT `customers_fk0` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);
</code></pre>

<p>Than, run  </p>

<pre><code>bin/cake bake all customers
</code></pre>

<hr>

<p><strong>EDIT</strong>
I've checked, the <a href="https://stackoverflow.com/questions/31319820">possible duplicate</a>, and changed my model</p>

<pre><code>$this-&gt;belongsTo('Customers', [
    'foreignKey' =&gt; 'customer_id',
    'joinType' =&gt; 'INNER'
]);
$this-&gt;hasMany('ChildCustomers', [ // &lt;-- changed this line
    'className' =&gt; 'Customers',    // &lt;-- added this line
    'foreignKey' =&gt; 'customer_id'
]);
</code></pre>

<p>Now, if I try to add/edit, no changes to the select. But if I view an existing customer, i got:</p>

<blockquote>
  <p>Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not
  unique table/alias: 'Customers'</p>
</blockquote>

<p>This is the code for displaying the select:</p>

<pre><code>echo $this-&gt;Form-&gt;input('customer_id');
</code></pre>

<p>This code was genereated by "bin/cake bake all customers"</p>

<hr>

<p><strong>EDIT 2</strong></p>

<p>I'll will summarize my current state: </p>

<p>Model: </p>

<pre><code>public function initialize(array $config)
{
    parent::initialize($config);

    $this-&gt;table('customers');
    $this-&gt;displayField('id');
    $this-&gt;primaryKey('id');

    $this-&gt;belongsTo('ParentCustomers', [
        'className' =&gt; 'Customers',
        'foreignKey' =&gt; 'customer_id',
        'joinType' =&gt; 'INNER'
    ]);
    $this-&gt;hasMany('ChildCustomers', [
        'className' =&gt; 'Customers',
        'foreignKey' =&gt; 'customer_id'
    ]);
}

public function buildRules(RulesChecker $rules)
{
    $rules-&gt;add($rules-&gt;existsIn(['customer_id'], 'ParentCustomers'));
    return $rules;
}
</code></pre>

<p>Controller: </p>

<pre><code>public function view($id = null)
{
    $customer = $this-&gt;Customers-&gt;get($id, [
        'contain' =&gt; ['ParentCustomers']
    ]);

    $this-&gt;set('customer', $customer);
    $this-&gt;set('_serialize', ['customer']);
}
</code></pre>

<p>If an user has an association (wife, child), the view displays a headline "Related Customers" on bottom of the page, but no additional table which shows the related customers. If an user has "customer_id = 0", the view said 'Record not found in table "customers"'.</p>

<p>I've also added </p>

<pre><code>$this-&gt;set('customers', $this-&gt;Customers-&gt;find('list'));
</code></pre>

<p>to the add() and edit() function, but I found no way to allow also an empty value.</p>

<p>For clarification:
Later, the front page (index()) should only list the "master customer" with customer_id = 0 with a small nested table, if he have a wife and/or children.</p>

<p>I think I am on the right way... Am I?</p>

<p>Thanks again in Advance</p>