Relationships with two databases

[powlam]

I am trying to use the package with related tables from 2 databases, but it fails. Digging into the query I realize that the databases names are not added.
Is there a work around for this problem?

[staudenmeir]

Hi @powlam,
Please share the relationship. Are these two databases on the same server and does the database user have access to both of them?

[powlam]

Thank you for answering.

Yes, the 2 DBs are in the same server, and the same database-user has access to both of them.

class OrganizationNode extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function humanResourcesNodes()
    {
        return $this->belongsToMany(HumanResourcesNode::class);
    }

    public function employees()
    {
        return $this->hasManyDeepFromRelations($this->humanResourcesNodes(), (new HumanResourcesNode())->employees());
    }
}

---

class HumanResourcesNodeOrganizationNode extends Model
{
    protected $table = 'human_resources_node_organization_node';

    public function humanResourcesNode()
    {
        return $this->belongsTo(HumanResourcesNode::class);
    }

    public function organizationNode()
    {
        return $this->belongsTo(OrganizationNode::class);
    }
}

---

class HumanResourcesNode extends Model
{
    public function employees()
    {
        return $this->hasMany(Employee::class);
    }
}

---

class Employee extends Model
{
    protected $connection = 'activeemployees';
}

OrganizationNode, HumanResourcesNodeOrganizationNode (pivot table) and HumanResourcesNode are in the main database ('mysql' connection), and Employee is in another one ('activeemployees' connection).

When trying to get $organizationNode->employees it fails; the generated query is:

select 
	`employees`.*, 
	`human_resources_node_organization_node`.`organization_node_id` as `laravel_through_key` 
from `employees` 
	inner join `human_resources_nodes` on `human_resources_nodes`.`id` = `employees`.`id`
	inner join `human_resources_node_organization_node` on `human_resources_node_organization_node`.`human_resources_node_id` = `human_resources_nodes`.`id` 
where `human_resources_node_organization_node`.`organization_node_id` = 59

(as you can see, the connection/DB name is not added, so it can't work properly).

I've verified that, adding the database name (from activeemployees.employees), it would work.

[staudenmeir]

I don't think I've tested it with this package, but the workaround I know is adding the database name to the table:

class Employee extends Model
{
    protected $connection = 'activeemployees';

    protected $table = 'activeemployees.employees';
}

[powlam]

We've replied at the same time... thank you. 👍

As I've tested, you have to remove the $connection from your answer.

[powlam]

I've found a way... The model of the external table must be defined in a different way: adding the database name to the $table instead of changing the $connection.

class Employee extends Model
{
    protected $table = 'activeemployees.employees';
}

This way the database name is added to all the queries involving the external table.

Credits to https://laracasts.com/discuss/channels/eloquent/how-to-properly-use-2-database-relationships?reply=603694