我们如何用一个SQL查询连接两个数据库?我更喜欢在这里使用sql,因为它可以提高性能。
config / database.yml
example:
adapter: postgresql
encoding: unicode
database: example_dev
example_report:
adapter: postgresql
encoding: unicode
database: example_report_dev
现在,在我的一个模型中,我想编写一个查询以从这两个数据库中获取数据
SELECT example_dev.*, example_report_dev.*
FROM example_dev.myTable AS firstdb
INNER JOIN example_report_dev.myTable AS seconddb
ON firstdb.id = seconddb.id
谢谢
一种方法是使用postgres的dblink
。
从文档中:
dblink在远程数据库中执行查询(通常是SELECT,但可以是返回行的任何SQL语句)。
首先,您需要使用以下命令dblink
在数据库中启用:
CREATE EXTENSION dblink;
然后,在您的应用程序中,您可以使用来执行原始查询语句ActiveRecord::Base.connection.execute
。
我在这里创建了此测试设置:
db1
包含tbl
带有field1
和field2
列的表。db2
包含tbl
带有field1
和field2
列的表。两者都有5行。
我的database.yml
:
development:
adapter: postgresql
encoding: unicode
database: db1
来自rails console
:
ActiveRecord::Base.connection.execute("
SELECT * FROM tbl
UNION ALL
SELECT * FROM dblink('dbname=db2','SELECT * FROM tbl') AS tbl2(field1 varchar, field2 int);
").to_a
# [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}, {"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]
这只是一种可能的方法。您可以设置dblink的连接字符串指向一个远程服务器(如:'dbname=yourdb port=5432 host=yourhost user=youruser password=yourpwd
)
请注意,这不是万能的方法。原始查询与模型无关。我建议您仅针对特定任务(例如运行报告)选择此选项。
如果您要对每个数据库查询一次,并且不想将其链接到模型,则可以使用ActiveRecord::Base.establish_connection
,如下所示:
conn1 = {
adapter: 'postgresql',
encoding: 'utf8',
database: 'db1'
}
conn2 = {
adapter: 'postgresql',
encoding: 'utf8',
database: 'db1'
#, more config here - other host, for instance #
}
arr1 = ActiveRecord::Base.establish_connection(conn1).connection.execute("select * from tbl").to_a
# => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}] => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]
arr2 = ActiveRecord::Base.establish_connection(conn2).connection.execute("select * from tbl").to_a
# => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}] => [{"field1"=>"one", "field2"=>1}, {"field1"=>"two", "field2"=>2}, {"field1"=>"three", "field2"=>3}, {"field1"=>"four", "field2"=>4}, {"field1"=>"five", "field2"=>5}]
您将获得两个数组arr1
,arr2
并同时包含两个数据。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句