how to show nested association value cakephp 3

Question

i want to run this code, where it has nested association data :

'VhpProducts.VhpHasilPencapaian',
'Departments.VhpHasilPencapaian',
'Groups.VhpHasilPencapaian' and to sum 'VhpHasilPencapaian.quantity'

but all I got was

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'VhpHasilPencapaian.amount' in 'field list'

$vhpCriteria = $this->VhpCriterias->find('all', [
    'contain' => [
        'VhpProducts.VhpHasilPencapaian',
        'Departments.VhpHasilPencapaian',
        'Groups.VhpHasilPencapaian'
    ],
    'field' => ['VhpProducts.name']
]);
$vhpCriteria->select([
    'product' => 'VhpProducts.name',
    'amountTotal' => $vhpHasilPencapaian->func()->sum('VhpHasilPencapaian.amount'),
    'qty' => $vhpHasilPencapaian->func()->sum('VhpHasilPencapaian.quantity'),
]);

Most likely `VhpHasilPencapaian` and/or the parent associations aren't 1:1/n:1, so it doesn't end up in the main query!? You should provide more information on the associations, and the resulting SQL query that you're looking for. — ndm, Oct 11 '17 at 15:01
@ndm i have VhpCriterias table that has associations (one to many) with VhpProduct, Departments and Groups. after that, I have VhpHasilPencapaian table that has associations with VhpProduct, Departments and Groups also. so i wanted to show data from VhpHasilPencapaian and summarize them — Yolanda Simamora, Oct 12 '17 at 02:22

score 0 · Answer 1 · answered Oct 11 '17 at 12:24

"Nested" associations should be written like this:

    $users = $this
        ->Users
        ->find('all')
        ->contain([
            'Roles' => [
                'Rights'
            ],
            'Posts' => [
                'Comments' => [
                    'Replies' => function($query) {
                        return $query
                            ->where(['public' => 1]);
                    }
                ]
            ]
        ])
    ->toArray();

They don't _have_ to, dot notation is supported too. – ndm Oct 11 '17 at 14:56 — ndm, Oct 11 '17 at 14:56

score 0 · Answer 2 · answered Oct 11 '17 at 19:38

0

You need to select all of the data you are going to use. Your field should be fields and include ALL fields you want returned. Alternatively you can exclude it entirely to return everything. If you do not have a field specified in the fields option you will not have it returned and you cannot use it.

$vhpCriteria = $this->VhpCriterias->find('all', [
    'contain' => [
        'VhpProducts.VhpHasilPencapaian',
        'Departments.VhpHasilPencapaian',
        'Groups.VhpHasilPencapaian'
    ],
    'fields' => ['VhpProducts.name', 'ALL OTHER FIELDS']
]);

$vhpCriteria->select([
    'product' => 'VhpProducts.name',
    'amountTotal' => $vhpHasilPencapaian->func()->sum('VhpHasilPencapaian.amount'),
    'qty' => $vhpHasilPencapaian->func()->sum('VhpHasilPencapaian.quantity'),
]);

answered Oct 11 '17 at 19:38

KaffineAddict

436
2
11

1

That doesn't matter on SQL level though, in the SQL query you can refer to any column of a table, irrespectively of whether it is in the `SELECT` list or not, the important part is that the table is actually included in the query. – ndm Oct 11 '17 at 23:37
@ndm good catch. I will have to test but I still thought CakePHP would run the first query and then run the select one that result almost like a sub select in which case you would need them to be selected correct? – KaffineAddict Oct 12 '17 at 14:17
1

CakePHP does run multiple queries in case of `hasMany` or `belongsToMany` associations, that is correct, but they aren't dependent on each other, except for that the additional queries will be fed with the primary/binding keys of their respective "parent" queries, and the queries for the associations need to select the respective foreign keys so that the results can be stitched together. The `SUM` function in this question is in the main query, so that's where the `VhpHasilPencapaian` table must be included. – ndm Oct 12 '17 at 14:29

how to show nested association value cakephp 3

2 Answers2