Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

@yuta_sawamura

CakePHP3で別々のデータベースのテーブルをjoinする方法

app.phpに接続情報を明記

2つの「datebase_aのcommentsテーブル」と「datebase_bのusersテーブル」が存在するとします。(usersテーブルとcommentsテーブルを別のデータベースで管理するのはあくまで例です)
1つのusersに複数のcommentsが紐づきます。

config/app.phpに接続情報を明記しましょう。

app.php
'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_a', 
        'password' => 'password',
        'database' => 'database_a',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
    'database_b' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'user_b', 
        'password' => 'password',
        'database' => 'database_b',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'log' => false,
    ],
],

モデルに定義

defaultConnectionName()を使いましょう。

UsersTable.php
namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class EstatesTable extends Table
{
    public static function defaultConnectionName(){
        return 'database_b';
    }

    public function initialize(array $config)
    {
        parent::initialize($config);
        $this->setTable('users');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');
        $this->addBehavior('Timestamp');
    }
}

別々のデータベースのテーブルをjoin

UsersController.php
$users = $this->Users->find()
    ->join([
        'table' => 'datebase_a.comments',
        'alias' => 'C',
        'type' => 'LEFT',
        'conditions' => 'C.user_id = Users.id',
    ])
    ->toArray();

参考:公式サイト

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
2
Help us understand the problem. What are the problem?