Association Problem: multiple contains with same name

Need Help
1

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.

2

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.

3

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

4

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.

5

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

$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.

$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 (4x) relationship

Like this

<?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'
    ]);
6

Zuluru and raul338,
Thanks a lot for your help.

The last aswer (raul338) fixed my problem.

Thanks you all.

7

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.