Laravel relations with composite, non-standard foreign keys

Question

I unfortunately need to import data from a third-party vendor and use their non-standard database schema with my laravel project. In addition, I need to store multiple "firms," each with their own set of users in my database.

I'm trying to figure out the best way (if it can be done) to use Eloquent to handle the relationships between these tables. So for instance, with my table structure like this:

 BmPerson
  'id',
  'firmId',
  'personId'

BmCoverage
  'id',
  'firmId',
  'personId',
  'securityId'

BmSecurity
  'id',
  'firmId',
  'securityId'

... for instance, I need to associate a "BmPerson" with many "BmSecurity" through the "BmCoverage" table.

But I need to somehow use composite keys, because I am storing multiple "firms" in each table (per the 3rd party vendor's database schema).

One approach I've used so far is scoping, e.g.: for my BmCoverage model:

 public function scopeFromFirm($query,$firmId){
  return $query->where('firmId','=',$firmId);//->where('personId','=',$personId);}

public function scopeFromPerson($query,$personId){
  return $query->where('personId','=',$personId);//->where('personId','=',$personId);}

Then I can retrieve the coverage list for an individual person, but I still need to somehow be able to associate the "BmCoverage" with the "BmSecurities." I suppose I could just add a scope the BmSecurities class too, but it would be nicer to just use Eloquent.

Has anyone come up with a good way to use composite keys in laravel model relationships, or should I just stick with the scoping method?

Answer 1

There is a package here that seems to be perfect for your case:

Compoships offers the ability to specify relationships based on two (or more) columns in Laravel 5's Eloquent. The need to match multiple columns in the definition of an Eloquent relationship often arises when working with third party or pre existing schema/database.

You would use it like this:

class BmPerson extends Model
{
    use \Awobaz\Compoships\Compoships;

    public function bmCoverages()
    {
        return $this->hasMany('App\BmCoverage', ['firmId', 'personId'], ['firmId', 'personId']);
    }
}

If every BmSecurity belongs to exactly one BmCoverage, and every BmCoverage belongs to exactly one BmPerson its probably easier to replace 'firmId', 'personId' with bmperson_id in BmCoverage DB; and 'firmId', 'securityId' with bmcoverage_id in BmSecurity. Then you can use default hasMany relations with one key.