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

问题描述

我正在上确实有3个不同的datascources一个cakePHP3项目。我有一个主要的模式，称为应用程序，它应该有两个 hasOne（）协会两款机型具有不同datascource作为模型中的应用。我创建了两个模型，并指出这两个型号的表到他们datascources与 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().

现在我添加了两个 hasOne（）关系到我的ApplicationsTable对象，并收到一个SQL错误，试图在应用程序 - ＆GT;得到（） 。这是明确的如SQL语句他们没有任何说明的数据源从和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框架/查询类和查询对象只似乎有一个数据源连接为一个类属性。

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.

有没有使用ORM蛋糕使用的数据检索不同datascources一种方式，或者我应该只使用自定义查询在这里？

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

在此先感谢！

推荐答案

目前，CakePHP的不创建联接时考虑数据源配置考虑，我不认为这会在不久的将来会增加，不尤其是因为跨数据库连接，不支持开箱即用（如，只是prePEND数据库名和你设置）的<一个href=\"http://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgres\">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.

假设你正在使用支持跨数据库连接一个数据库管理系统，你可以做的就是改变使用表名称包括数据库名也一样，即 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的，你可以得到这方面的工作相当容易使用 附加数据库语句 ，如可以在链接的答案上面看到的。在你的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 Cake\Datasource\ConnectionManager;

// ...

/* @var $connection \Cake\Database\Connection */
$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屋！