使用关系和连接选择数据库行

Question

I'm trying to select some data from the database using Laravel's Eloquent class for my model.

I have tried the following in order to change the database connection used to the test-connection: $users = Users::on('test')->with('posts')->get();

My database connections are the following: (Note: the only difference is the table prefix (prefix))

    'default' => array(
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'database'  => 'database',
        'username'  => 'username',
        'password'  => 'password',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

    'test' => array(
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'database'  => 'database',
        'username'  => 'username',
        'password'  => 'password',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => 'test_',
    ),

The problem is that when running the above code ($users = Users::on('test')->with('posts')->get();) then it runs the following SQL:

select * from `test_users`
select `posts`.*, `users_posts`.`user_id` as `pivot_user_id`, `users_posts`.`post_id` as `pivot_post_id` from `posts` inner join `users_posts` on `posts`.`id` = `users_posts`.`post_id` where `users_posts`.`post_id` in ('1', '2', '3')

In the results there are no posts, due to that it takes posts from the table posts instead of test_posts, and so on for users_posts.

The method for getting users posts in the user model is the following:

public function posts() {
    return $this->belongsToMany('Users', 'users_posts', 'user_id', 'post_id');
}

Is this a bug or how can I get this to work for me? Any ideas?

Answer 1

I found a pull request on Laravel/Framework which makes it possible to set the connection behavior of Model Relations.

I integrated this into my package and it worked.

The author gives some examples of usage:

Examples

Application configs: database.php

return [
    'default' => 'conn1',

    'connections' => [
        'conn1' => [ ****** ], 
        'conn2' => [ ****** ]
    ]
];

Parent Model

class Parent extends Eloquent
{
    public function children()
    {
        return $this->belongsTo('Child', 'parent_id', 'id');
    }
}

Behavior Before:

Parent::with('children')->get();
// model Parent $connection = 'conn1';
// model Child $connection = 'conn1';


Parent::on('conn2')->with('children')->get();
// model Parent $connection = 'conn2';
// model Child $connection = 'conn1'; (default connection)

After fix:

Parent::with('children')->get();
// model Parent $connection = 'conn1';
// model Child $connection = 'conn1';

Parent::on('conn2')->with('children')->get();
// model Parent $connection = 'conn2';
// model Child $connection = 'conn1';  (default connection)

Parent::on('conn2', true)->with('children')->get();
// model Parent $connection = 'conn2';
// model Child $connection = 'conn2'; (connection of parent model)