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?
join
(after the method with the same name), notjoins
– ndm