Selecting Database Rows Using Relationships and Joins
I am trying to fetch some data from a database using the Laravel Eloquent class for my model.
I tried the following to change the database connection used for the test
-connection:$users = Users::on('test')->with('posts')->get();
My database connections are as follows: (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 you run the above code ( $users = Users::on('test')->with('posts')->get();
), 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')
Not in the results posts
, because of this it takes posts
from the table posts
instead test_posts
, etc. for users_posts
.
The method for getting messages from users in a custom model looks like this:
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?
source to share
I found a pull request on Laravel / Framework that allows setting the join behavior of model relationships.
I included this in my package and it worked.
The author gives several examples of use:
Examples of
Application configurations: 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 the 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)
source to share