Display all tables fields including hasMany associations in CakePHP 3

Question

I want to fetch all the contents and display them in one table from the tables below:

Petrolclaims table

    $this->belongsTo('Workers', [
        'foreignKey' => 'worker_id',
        'joinType' => 'INNER'
        //'through' => 'Selections'
    ]);

    $this->belongsTo('Vehicles', [
        'foreignKey' => 'vehicle_id',
        'joinType' => 'INNER'
    ]);

Tollclaims table

    $this->belongsTo('Workers', [
        'foreignKey' => 'worker_id',
        'joinType' => 'INNER'
    ]);

    $this->belongsTo('Vehicles', [
        'foreignKey' => 'vehicle_id',
        'joinType' => 'INNER'
    ]);

Workers table

<?php
namespace App\Model\Table;

use App\Model\Entity\Worker;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

/**
 * Workers Model
 *
 */
class WorkersTable extends Table
{

/**
 * Initialize method
 *
 * @param array $config The configuration for the Table.
 * @return void
 */
public function initialize(array $config)
{
    parent::initialize($config);

    $this->hasMany('Petrolclaims', [
        'className' => 'Petrolclaims'
    ]);

    $this->hasMany('Tollclaims', [
        'className' => 'Tollclaims'
    ]);

    $this->table('workers');
    $this->displayField('name');
    $this->primaryKey('id');

}

/**
 * Default validation rules.
 *
 * @param \Cake\Validation\Validator $validator Validator instance.
 * @return \Cake\Validation\Validator
 */
public function validationDefault(Validator $validator)
{
    $validator
        ->add('id', 'valid', ['rule' => 'numeric'])
        ->allowEmpty('id', 'create');

    $validator
        ->requirePresence('name', 'create')
        ->notEmpty('name');

    $validator
        ->add('createdDate', 'valid', ['rule' => 'datetime'])
        ->allowEmpty('createdDate', 'create');

    return $validator;
}

}

Vehicles table

    $this->hasMany('Petrolclaims', [
        'className' => 'Petrolclaims'
    ]);

    $this->hasMany('Tollclaims', [
        'className' => 'Tollclaims'
    ]);

WorkersController

    $results1 = $this->Workers->find('all')
    ->select([
        'Petrolclaims.worker_id', 'Petrolclaims.vehicle_id'
        'Tollclaims.worker_id', 'Tollclaims.vehicle_id', 
        'Workers.id',
        'Vehicles.id'
    ])
    ->order(['Workers.name' => 'ASC'])
    ->contain([
        'Tollclaims','Petrolclaims'
    ]);

How can I achieve this?

Inigo Flores · Accepted Answer · 2016-02-03T20:50:28.910

1

See if this works:

$results1 = $this->Workers->find()
    ->select(['id','name'])
    ->contain([
        'Petrolclaims'=>function ($q) {
            return $q
                ->select(['id','worker_id','vehicle_id'])
                ->autoFields(false);
        },
        'Tollclaims'=>function ($q) {
            return $q
                ->select(['id','worker_id','vehicle_id'])
                ->autoFields(false);
        },
    ])
    ->order(['name' => 'ASC']);


    // EDIT: hasMany data can be accesed by doing the following:
    foreach ($results1 as $worker) {
        echo $worker->petrolclaims[0]->vehicle_id;
    }

_{Note: The above code is untested.}

You don't need to fetch data from table Vehicles, as you only need Vehicles.id, which is already contained in the foreign keys Petrolclaims.vehicle_id and Tollclaims.vehicle_id.

edited Feb 03 '16 at 20:50

answered Jan 26 '16 at 14:03

Inigo Flores

4,461
1
15
36

I used ur code and when i try to print the $results1, The output: SELECT Workers.id AS `Workers__id`, Workers.name AS `Workers__name` FROM workers Workers ORDER BY Workers.name ASC why it does not include the field in the petro and claim table? or how should i call the content? thank you for your time.. – WoonWG Jan 27 '16 at 03:27
hi, still output the same results as I mentioned previously. Are those table relationship correctly define? – WoonWG Jan 28 '16 at 02:01
Same, It will only select the content from the Workers table. The other two tables wasn't detected. Any suggestion? Thank you. – WoonWG Jan 29 '16 at 01:42
Are you sure CakePHP is loading the class from the file? Make sure the path and filie name is the correct. A trick is to include garbage in the code to force a syntax error. If no syntax error is thrown, then you might have the file name wrong, and CakePHP is using instead a generic model. – Inigo Flores Feb 01 '16 at 12:58
i change the hasMany to hasMasny, it shown 'Unknown method "hasMasny" ', is this test prove that it loads from the class? – WoonWG Feb 02 '16 at 01:50
Yep, it's loading file. I have reread your comments, and I believe the code is working as expected. Relationships of the type `hasMany` produce no joins. Instead, results are fetched in separate queries. I've edited the code. See if it works. – Inigo Flores Feb 03 '16 at 20:51
hey it works. So it always had been query correctly, just that I retrieve it in a wrong approach. lol.. thanks for your patience and time. by the way, since you say the the petro and toll table contain the foreign key for the Vehicles table, so how can i call to retrieve the content in Vehicles table? – WoonWG Feb 04 '16 at 06:37
I'm glad it works! To load `Vehicles`, try adding it to the `contain()` array. You have to include both `'Petrolclaims.Vehicles'` and `'Tollclaims.Vehicles'`. – Inigo Flores Feb 04 '16 at 13:49
It is working now. But i have an issue, i ghope this is the last one. haha. For my case, let's say I have 3 workers, since each workers can have many petrol claims for example, but it only shows one claim record for each user. emm do you get my problem? – WoonWG Feb 05 '16 at 02:57
hey thanks a lot. I manage to solve that issue already. Thanks for your patience in helping me to solve the problem. – WoonWG Feb 05 '16 at 07:07
One more things, for the sorting part, i can't sort my vehicles table, any way to work out on this? ?sort=Petrolclaims.Vehicles.date&direction=asc – WoonWG Feb 05 '16 at 08:01
In pagination you mean? To sort for an associated model, see [this answer](http://stackoverflow.com/a/28961659/3144159). – Inigo Flores Feb 05 '16 at 08:36

Display all tables fields including hasMany associations in CakePHP 3

1 Answers1