# Association Problem: multiple contains with same name

**URL:** <https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937>\
**Category:** Need Help\
**Created:** [June 12, 2020, 9:58pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937 "2020-06-12T21:58:24Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![cavatoni](https://avatars.discourse-cdn.com/v4/letter/c/a698b9/32.png) [@cavatoni](https://discourse.cakephp.org/u/cavatoni)\
**Post date:** [June 12, 2020, 9:58pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/1 "2020-06-12T21:58:24Z")

</div>

Hello,  
I have 4 tables:

TABLE A =\> [id, …]  
TABLE B =\> [id, TableA\_id, …]  
TABLE C =\> [id, TableA\_id, …]  
TABLE D=\>[id, TableB\_id, TableC\_id, …]

Fine…now I made:

`results = tableDTable->find()->contain([TableB, TableC, TableB.TableA, TableC.TableA])`

When I debug the query, the JOIN - TableB.TableA, TableC.TableA - do not exists…

How can I fix it, please?

Thanks a lot.

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 12, 2020, 10:35pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/2 "2020-06-12T22:35:50Z")

</div>

When I do this, I always write it like:

```
->contain(['TableB' => ['TableA'], 'TableC' => ['TableA']])

```

and the data structure will have `$results->table_b`, `$results->table_b->table_a`, `$results->table_c` and `$results->table_c->table_a`.

---

<div class="post-metadata">

**Author:** ![cavatoni](https://avatars.discourse-cdn.com/v4/letter/c/a698b9/32.png) [@cavatoni](https://discourse.cakephp.org/u/cavatoni)\
**Post date:** [June 13, 2020, 12:37pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/3 "2020-06-13T12:37:08Z")

</div>

Thanks for your help.

But i still have problems with multiple contains with same name.

I have no problem to access the data using $results-\>table\_b-\>table\_a…etc…

But, i have to order my result, and I’m getting the error: "Unknown column TableA.name ".

When I debug the query, the JOIN associatiation “TableB.TableA…and…TableC.TableA” is not there.

Very strange.

Thanks a lot

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 13, 2020, 5:16pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/4 "2020-06-13T17:16:56Z")

</div>

What does the query look like? Associations are not always joined into the main query, depending on various options, none of which you’ve shown.

---

<div class="post-metadata">

**Author:** ![raul338](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/raul338/32/69_2.png) [@raul338](https://discourse.cakephp.org/u/raul338)\
**Post date:** [June 13, 2020, 5:29pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/5 "2020-06-13T17:29:25Z")

</div>

> [@cavatoni](#):
>
> But, i have to order my result, and I’m getting the error: "Unknown column TableA.name ".
> 
> When I debug the query, the JOIN associatiation “TableB.TableA…and…TableC.TableA” is not there.

I think the join is not there because you are loading a `hasMany` relationship, which results in another query.  
One way to avoid these results you have to use `belongsTo` or `hasOne` relationships.

If you want to order the contain results, you can pass a function on the contain call

```php
$this->TableD->find('all')
    ->contain([
        'TableC' => [
            'TableA' => function (\Cake\ORM\Query $query) {
               return $query->order(['TableA.name' => 'ASC']);
            },
        ],
    ]);

```

If you want to order the main results, you can use leftJoinWith and contain at the same time.

```php
$query = $this->TableD->find('all')
    ->leftJoinWith('TableC.TableA')
    // I don't think next line will work, best use belongsToMany
    // ->leftJoinWith('TableB.TableA')
    ->contain([
        'TableC' => ['TableA'],
        'TableB' => ['TableC'],
    ]);
$query = $query
    ->select([
        'count_a' => $query->func()->count('TableA.id')
    ])
    ->group(['TableC.id'])
    ->order([
        'count_a' => 'DESC',
    ]);
    // Gives TableD sorted by most TableB.TableA records

```

If you want to leftJoin tableA on both tables, you have to use [belongsToMany](https://book.cakephp.org/3/en/orm/associations.html#belongstomany-associations) ([4x](https://book.cakephp.org/4/en/orm/associations.html#belongstomany-associations)) relationship

Like this

```php
<?php
// on src/Model/Table/DTable.php initialize method
$this->belongsToMany('BA', ['className' => 'TableA'])
    ->setThrough('tableB');

$this->belongsToMany('CA', ['className' => 'TableA'])
    ->setThrough('tableC');

// To use it, anywhere else....
$this->TableD->find('all')
    ->leftJoinWith('BA')
    ->leftJoinWith('CA')
    ->contain([
        'TableB' => ['TableA'], // produces $row->table_b[]->table_A[]->id
        'TableC' => ['TableA'], // produces $row->table_b[]->table_A[]->id

        'BA', // produces $row->ba[]->table_b->id; ba is table_a record linked with table b
        'CA', // produces $row->ca[]->table_c->id; ca is table_a record linked with table c
    ])
    ->order([
        // eg.
        'BA.name',
        'CA.name'
    ]);

```

---

<div class="post-metadata">

**Author:** ![cavatoni](https://avatars.discourse-cdn.com/v4/letter/c/a698b9/32.png) [@cavatoni](https://discourse.cakephp.org/u/cavatoni)\
**Post date:** [June 13, 2020, 6:45pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/6 "2020-06-13T18:45:01Z")

</div>

Zuluru and raul338,  
Thanks a lot for your help.

The last aswer (raul338) fixed my problem.

Thanks you all.

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [June 13, 2020, 8:15pm UTC](https://discourse.cakephp.org/t/association-problem-multiple-contains-with-same-name/7937/7 "2020-06-13T20:15:52Z")

</div>

If TableB has a column called TableA\_id, then that would appear to call for a `belongsTo` relation, not `hasMany`. In which case, this all seems like more than necessary, and a standard contain like I showed should work.