Eloquent ManyToMany with an alternative key

Question

I have a table Subscribers and a table Services. A subscriber can have multiple services, and a service can have multiple subscribers.

Only the problem is that I can't get any services by the subscriber. If I look into the SQL it does work. The subscriber is connected to the service with the pcs_id.

So how can i get the 'many-to-many' relationship to work?

Subscribers table:

| Column | Type        | Key                         |
|--------|-------------|-----------------------------|
| id     | Int         | Primary Key, Auto increment |
| pcs_id | Varchar(50) | Unique Key                  |
| email  | varchar     | Unique Key                  |

Services table:

| Column | Type    | Key                         |
|--------|---------|-----------------------------|
| id     | int     | Primary Key, Auto Increment |
| name   | varchar | Unique Key                  | 

service_subscriber table:

| Column            | Type        | Key                         |
|-------------------|-------------|-----------------------------|
| id                | int         | Primary Key, Auto Increment |
| subscriber_pcs_id | varchar(50) | Index                       |
| Service_id        | int         | Index                       |

Subscriber model:

public function Services() {
    return $this->belongsToMany('App\Service', 'service_subscriber','subscriber_pcs_id','service_id');
}

Service model:

public function Subscribers() {
    return $this->belongsToMany('App\Subscriber', 'service_subscriber','service_id', 'subscriber_pcs_id');
}

If I ask for the services, I get an empty collection; and if I ask for the SQL I get a working SQL string.

$subscriber = Subscriber::where('pcs_id', 'TEST_97988471')->first();
$services = $subscriber->Services()->toSql();
// SQL: select * from `services` inner join `service_subscriber` on `services`.`id` = `service_subscriber`.`service_id` where `service_subscriber`.`subscriber_pcs_id` = ?

I think it has something to do with the pcs_id that it is a string. For the models I cannot turn off the auto increment, because otherwise I lose the ids which I need elsewhere.

Answer 1

Make pcs_id the primary key and add in Subscriber model:

protected $primaryKey = "pcs_id";

By default, it assumes id as primary key, and a FOREIGN KEY subscriber_pcs_id is actually pointing to a PRIMARY KEY id.