1

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

hhelderneves
  • 925
  • 8
  • 24

2 Answers2

1

Found an workaround. Seems that Many-To-Many only works in 1 direction (?).

Github Issue

Workaround

Thanks for all the help.

hhelderneves
  • 925
  • 8
  • 24
0

You need to tell Eloquent that you want to use other db, try something like this

return $this->belongsToMany('App\Models\Hub\Sites\Site', 'sgc.sgc_contracts_templates_hasmany_sites', 'template_id', 'site_id');

and then check if it tries to query sgc db.

If it still don't help try this https://stackoverflow.com/a/60060726/7892040.

KKK
  • 61
  • 1
  • 8