Tables not joining using options to find()

Question

I am trying to join the following two tables in a controller:

artifacts_publications
----------------------
id
artifact_id
publication_id

and

authors_publications
--------------------
id 
author_id
publication_id

Other tables which have associations like belongsTo and HasMany set up easily join using contain(). For these tables, suppose no association is already defined. In the end, I want to join another table to the join of these tables but for now I cannot get these two joined.

The Controller uses

    namespace App\Controller;
    use App\Controller\AppController;
    use Cake\ORM\TableRegistry;
    use Cake\Datasource\ConnectionManager;

In the index() action of the controller, I have

$options['joins'] = [
    [
        'table' => 'authors_publications',
        'alias' => 'j',
        'type' => 'LEFT OUTER',
        'conditions' => [
            'authors_publications.publication_id = artifacts_publications.publication_id',
        ],
    ],
];

$query = TableRegistry::getTableLocator()->get('artifacts_publications')->find('all', $options);
$query->limit(2);

debug($query);
debug($query->toArray());

The resulting SQL query is somewhat like this, but I have shortened long identifiers:

SELECT id, artifact_id, publication_id 
FROM artifacts_publications
LIMIT 2;

Executing that query also shows no join. I tried to follow cakePHP table joining two tables issue but I still cannot join. Hand-written SQL worked but it is not elegant. What am I missing here?

The option is named join (after the method with the same name), not joins — ndm
I cannot believe I missed that for a few days. It works now. — Utsav Munendra

Rainier Rainier · Accepted Answer · 2019-08-12T08:18:17

@Utsav

Try to use the Containable behaviour of CakePHP by switching the 'join' with 'contain'.

$options = [
            'fields' => [
                'ArtifactsPublication.fieldsYouWant'
            ],
            'conditions' => [
                'ArtifactsPublication.id' => $artifactId
            ],
            'contain' => [
                'AuthorsPublication'
            ]
        ];

You can also set the recursive to define if you want to get the contained model's linked models. Alternativly you can specify in the fields option what fields you want from the artifacts_publications Model and then just contain the authors_publications Model.

Hope this helps as I have had bad experiences with manual joins in CakePHP, also the CakePHP standard is to use contain. Just note a contain cannot retrieve distant joined data.

Cheers mate :)

Tables not joining using options to find()

1 Answers