need some light on a problem... I'm trying to get data from another database using a Many-To-Many relation.
Basically, a site can have many templates and a template can have many sites.
Site Model:
class Site extends Model
{
use HasFactory;
/**
* Database Connection Name
*/
protected $connection = 'hub';
/**
* Model Table Name
*/
protected $table = 'tbl_sites';
/**
* Model Primary Key
*/
protected $primaryKey = 'id';
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'code', 'name', 'abbreviation', 'address', 'zipcode', 'town', 'geolocation_id', 'gps'
];
/**
* Returns associated SGC templates
* @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
*/
public function sgc_templates()
{
return $this->belongsToMany('App\Models\SGC\Contracts\Templates\Template', 'sgc_contracts_templates_hasmany_sites', 'site_id', 'template_id');
}
}
Template Model:
class Template extends Model
{
use HasFactory;
/**
* Database Connection Name
*/
protected $connection = 'sgc';
/**
* Model Table Name
*/
protected $table = 'sgc_contracts_templates';
/**
* Model Primary Key
*/
protected $primaryKey = 'id';
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'name', 'description', 'file_name'
];
/**
* Returns associated sites
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
*/
public function sites()
{
return $this->belongsToMany('App\Models\Hub\Sites\Site', 'sgc_contracts_templates_hasmany_sites', 'template_id', 'site_id');
}
}
If I try to get templates associated to a site with: Site::with('sgc_templates')->find(1), everything works fine.
If I try to get sites associated to a template with: Template::with('sites')->find(1), I got error. Basically saying that the pivot table doesn't exists on sites database. The templates and the pivot table are on sgc connection/database.
The error is:
Illuminate\Database\QueryException
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'hub.sgc_contracts_templates_hasmany_sites' doesn't exist (SQL: select `tbl_sites`.*, `sgc_contracts_templates_hasmany_sites`.`template_id` as `pivot_template_id`, `sgc_contracts_templates_hasmany_sites`.`site_id` as `pivot_site_id` from `tbl_sites` inner join `sgc_contracts_templates_hasmany_sites` on `tbl_sites`.`id` = `sgc_contracts_templates_hasmany_sites`.`site_id` where `sgc_contracts_templates_hasmany_sites`.`template_id` in (1))
Clearlly that the Template::with('sites')->find(1) is going to the wrong database, because on the error, 'hub.sgc_contracts_templates_hasmany_sites' should be 'sgc.sgc_contracts_templates_hasmany_sites'.
Can someone help me with this? :|
Thanks
My Answer:
To solve this issue, you can specify the connection for the pivot table in the relationship definition in your Template model. You can do this by passing the connection name as the fourth argument in the belongsToMany method.
Here's how you can update your Template model:
php
/**
* Returns associated sites
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
*/
public function sites()
{
return $this->belongsToMany('App\Models\Hub\Sites\Site', 'sgc_contracts_templates_hasmany_sites', 'template_id', 'site_id', 'id', 'id', 'hub');
}
By passing 'hub' as the fourth argument in the belongsToMany method, you are specifying that the pivot table 'sgc_contracts_templates_hasmany_sites' is located in the 'hub' connection/database.
Now, when you call Template::with('sites')->find(1), Laravel will look for the pivot table in the 'hub' connection/database instead of the default connection.
This should resolve the issue and allow you to retrieve sites associated with a template from a different database.
Rate this post
4 of 5 based on 8791 votesComments