在我的应用程序中引用第二个数据库/数据源时出现问题

我在将第二个数据库合并到我的应用程序中时遇到问题。

1. 我在中创建了两个连接 app.php 和 app_local.php
2. 模型(即。 BusinessRequests )引用第二个数据库实现 defaultConnectionName()

错误

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'app_a.business_requests' doesn't exist

结果SQL-注意引用时缺少数据库/连接前缀 业务请求 在LEFT JOIN中

SELECT
  Requests.id AS Requests__id,
  Requests.business_request_id AS Requests__business_request_id,
  Requests.notes AS Requests__notes,
  Requests.created AS Requests__created,
  Requests.modified AS Requests__modified,
  BusinessRequests.id AS BusinessRequests__id,
  BusinessRequests.title AS BusinessRequests__title,
  BusinessRequests.status AS BusinessRequests__status,
  BusinessRequests.created AS BusinessRequests__created,
  BusinessRequests.modified AS BusinessRequests__modified
FROM
  requests Requests
  LEFT JOIN business_requests BusinessRequests ON BusinessRequests.id = Requests.business_request_id
WHERE
  Requests.id = 1
LIMIT
  1

数据库 app_a (注意外键设置)

CREATE TABLE `requests` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `business_request_id` int(11) unsigned DEFAULT NULL,
  `notes` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `FK_requests_app_common.business_requests` (`business_request_id`),
  CONSTRAINT `FK_requests_app_common.business_requests` FOREIGN KEY (`business_request_id`) REFERENCES `app_common`.`business_requests` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

数据库 app_common

CREATE TABLE `business_requests` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70270 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

的内容 app.php

  'Datasources' => [
    'default' => [
        'className' => Connection::class,
        'driver' => Mysql::class,
        'persistent' => false,
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
    ],

    'common' => [
        'className' => Connection::class,
        'driver' => Mysql::class,
        'persistent' => false,
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,
        'quoteIdentifiers' => false,
    ],
],

的内容 app_local.php (mariadb是Docker容器)

'Datasources' => [
        'default' => [
            'host' => 'mariadb',
            'username' => 'root',
            'password' => 'root',
            'database' => 'app_a',
            'url' => env('DATABASE_URL', null),
        ],

        'common' => [
            'host' => 'mariadb',
            'username' => 'root',
            'password' => 'root',
            'database' => 'app_common',
            'url' => env('DATABASE_URL', null),
        ],
    ],

的内容 BusinessRequestsTable.php

在这里,我指定了用于模型的连接。

public static function defaultConnectionName(): string
{
    return 'common';
}

的内容 RequestsController.php

public function view($id = null)
{
    $request = $this->Requests->get($id, [
           'contain' => ['BusinessRequests'],
    ]);

    $this->set(compact('request'));
}

潜在解决方案

在模型中设置表时设置数据库的前缀:

public function initialize(array $config): void
{
    parent::initialize($config);

    $this->setTable('app_common.business_requests');
    $this->setDisplayField('title');
    $this->setPrimaryKey('id');

    $this->addBehavior('Timestamp');
}