如何使用 cakephp3 在查询中使用不同的数据源? [英] How to use different datasources in a Query using cakephp3?

问题描述

我正在开发一个 cakePHP3 项目，该项目确实有 3 个不同的数据源.我有一个主模型，称为应用程序，它应该有两个 hasOne() 关联到两个具有不同数据源的模型作为模型应用程序.我已经创建了两个模型，并使用 defaultConnectionName() 将两个模型表指向了它们的数据源.

I'm working on a cakePHP3 project which does have 3 different datascources. I have one main model, called application, which should have two hasOne() associations to two models with a different datascource as Model Application. I have created the two models and pointed the two Model Tables to their datascources with defaultConnectionName().

现在我向 ApplicationsTable 对象添加了两个 hasOne() 关系，并在尝试 Applications->get() 时收到 sql 错误.这很清楚，因为在 SQL 语句中，它们没有对 FROM 和 JOIN 部分的数据源进行任何说明，例如 SELECT * FROM datasource1.myTable

Now I added two hasOne() relations to my ApplicationsTable object and recieve an sql error, when trying to Applications->get(). This is clear as in the SQL Statement their isn't any stating of a datasource on the FROM and JOIN part, like SELECT * FROM datasource1.myTable

我查看了 cakephp 框架 ORM/Query 类，Query 对象似乎只有一个数据源连接作为类属性.

I had a look into the cakephp framework ORM/Query class and the Query object only seems to have one datasource connection as a class attribute.

有没有办法在使用 cake ORM 的数据检索中使用不同的数据源，还是应该在这里使用自定义查询?

Is there a way to use different datascources in data retrieval using cake ORM or should I just use a custom query here?

提前致谢！

推荐答案

目前CakePHP在创建join时没有考虑数据源配置，我认为近期不会增加，不会至少是因为在 Postgres 和 SQLite.

For now, CakePHP doesn't take datasource configurations into account when creating joins, and I don't think that this will be added in the near future, not least because cross database joins aren't supported "out of the box" (as in, just prepend the database name and you're set) in Postgres and SQLite.

假设您使用的是支持跨数据库连接的 DBMS，您可以做的是更改使用的表名以包含数据库名，即 databaseName.tableName 而不是 tableName

Assuming you are using a DBMS that supports cross DB joins, what you could do is change the used table name to include the database name too, ie databaseName.tableName instead of just tableName

public function initialize(array $config)
{
    $this->table('databaseName.tableName');
    // ...
}

或动态

$this->table($this->connection()->config()['database'] . '.tableName');

SQLite

对于 SQLite，您可以使用 ATTACH DATABASE 语句，如上面链接的答案所示.在您的 CakePHP 应用程序中，您可以在引导程序中或任何需要 id 的地方发出此语句，例如

SQLite

For SQLite you can get this working rather easily using the ATTACH DATABASE statement, as can be seen in the linked answer above. In your CakePHP application, you could issue this statement in your bootstrap or wherever you need id, something like

use CakeDatasourceConnectionManager;

// ...

/* @var $connection CakeDatabaseConnection */
$connection = ConnectionManager::get('default');
$connection->execute('ATTACH DATABASE "db2.sqlite3" AS databaseName');

将附加数据库 db2.sqlite3 与模式名称 databaseName.从那以后，上面提到的表名解决方案应该可以正常工作，至少是非动态的，因为动态的会使用类似 db2.sqlite3 的东西作为模式名称，这不起作用.

which would attach the database db2.sqlite3 with a schema name of databaseName. From there on, the above mentioned table name solution should work fine, at least the non-dynamic one, as the dynamic one would use something like db2.sqlite3 as the schema name, which wouldn't work.

我不习惯 Postgres，所以在这一点上我不能给你一个例子，但它应该可以使用 外部数据包装器，即首先发出正确的语句，然后只引用指定的模式名称.

I'm not used to Postgres, so at this point I can't give you an example, but it should probably work similarily using foreign data wrappers, ie issue the proper statements initially, and then just refer to the specified schema name.

这篇关于如何使用 cakephp3 在查询中使用不同的数据源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！