Laravel 4: joining two tables using Eloquent for multiple database connections
I am having a problem joining two tables using Eloquent for multiple database connections (example master database, DB1, DB2, DB3, etc.). Let me clarify briefly below: -
Suppose I have two tables 1. Categories and 2. Products. The models for both tables are shown below: -
1) Category.php
Class categoryexpands pronounced {
public $timestamps = false;
protected $table = 'categories;
protected $fillable = array('v_name','e_status');
public function products()
{
return $this->belongsTo(Product,'i_category_id');
}
}
2) Product.php
class Product extends Eloquent {
public $timestamps = false;
protected $table = products;
protected $fillable = array('v_name',’i_category_id’,'e_status');
public function categories()
{
return $this->belongsTo(Category,'i_category_id');
}
}
Now, in ProductController.php
$objProduct = new Product;
$objProduct->setDBConnection($objdataAuth->v_db_name); // dynamic database connection name
$arrProductDetail = $objProduct->get()->section_activities()->get();
$ arrProductDetail does not retrieve information related to a category (such as a dynamic database). But it fetches the category of the main database (for example in the /database.php app). If we take $ objProduct-> get (), then it fetches the entire product of the new database (DB1, DB2 ....) But after some rnd we found that the eloquent ORM uses multiple select queries and not a join.
What is our concept: we have one main database and another dynamic database created from the system. We need to hook up multiple database tables for some functionality that we cannot do right now. We cannot override the new dynamic database join.
Do we have any solution? Also does Laravel provide functions to close / destroy the previous connection and connect a new DB (like Core PHP)?
thank
Please add below code to app / database.php
'mysql_dynamic_connection' => array(
'driver' => 'mysql',
'host' => 'host',
'database' => 'db_name',
'username' => 'username',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
Now add below line to both product and model categories,
protected $connection = 'mysql_dynamic_connection';
Now set up a database connection in the controller file as
Config::set('database.connections.mysql_dynamic_connection.database', $objdataAuth->v_db_name);
where mysql_dynamic_connection = another database connection in app / database.php and $ objdataAuth-> v_db_name = Your database name
$objProduct = new Product;
$arrProductDetail = $objProduct->where('id','=',1)->first()->categories()->get();
echo '<pre>'; print_r($arrProductDetail);
You will get an array of product categories with ID 1.
Thanks, Monang Shah
source to share