如何使用Laravel符号在不同服务器上的多个数据库之间执行连接查询?

This is how we handle multiple DB Connections with Laravel, which is a PHP Framework not Python (For whom thinks that this is a duplicate post)

这就是我们使用Laravel处理多个DB连接的方式，Laravel是一个PHP框架，而不是Python(对他来说，这是一个重复的帖子)

<?php
return array(

    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'host1',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'host2',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

And this is how you connect to this database.

这就是你连接这个数据库的方式。

$user1 = User::on('mysql1')->where(/* ... */)->get()
$user2 = User::on('mysql2')->where(/* ... */)->get()

These are only SELECT queries. Therefore Eloquent works flawlessly.

这些只是SELECT查询。因此雄辩的完美作品。

However, when I want to execute a JOIN query operation between these 2 databases, this seems not possible.

然而，当我想在这两个数据库之间执行连接查询操作时，这似乎是不可能的。

3 个解决方案

#1

Basically you can not do this using Eloquent ORM or Laravel's query builder.

基本上，您不能使用雄辩的ORM或Laravel的查询构建器来实现这一点。

However, you can try to use raw expression (https://github.com/laravel/framework/blob/8865d2276be94f0a3497ba3bd26c906ae9e91e1c/src/Illuminate/Database/Query/Expression.php#L13 ) or smth like this, but you'll have to write code to make a model from query result.

但是，您可以尝试使用原始表达式(https://github.com/laravel/framework/blob/8865d2276be94f0a3497ba3bd26c266e91e91c / src/illuminate/query/expression.php #L13)或smth，但您必须编写这样的查询代码。

If you use laravel 4 this question can help: Laravel 4: how to run a raw SQL?

如果您使用laravel 4，这个问题可以帮助您:laravel 4:如何运行原始SQL?

#2

Write a raw query and use laravels DB methods to escape data.

编写一个原始查询并使用laradb方法来转义数据。

$results = DB::select( DB::raw("SELECT Users.id, Properties.name FROM [Database1].[dbo].[Users] join [Database2].[dbo].[Properties] on Users.id = Properties.user_id where [so].[dbo].[Users].id = :user_id"), array('user_id' => Input::get('user_id', -1)));

#3

If these 2 databases are on different servers, you can't do this. At least not simply. I read about a storage engine. But I have never tried to do this before.

如果这两个数据库在不同的服务器上，你不能这么做。至少不简单。我读过关于存储引擎的文章。但我以前从未尝试过这样做。

BUT

但

If there databases are on the same server, you can do it with a simple join, IF the user has the right permission!

如果在同一台服务器上有数据库，您可以使用一个简单的连接，如果用户有正确的权限!

#1