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.

Answer 2

Everything you need for this can be found here https://laravel.com/docs/5.2/eloquent-relationships

You can easily define which cols sohuld be the referenced key.

Example:

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

This would probably belong to your App\Firm or whatever it is called.

In general the hasMany relations looks like this

return $this->hasMany('App\Comment', 'foreign_key', 'local_key');

As you can see you can specify the keys.

Answer 3

As the others have said, you need to use the HasMany and HasManyThrough relationship.

Here from your table definitions, you simply need access to:

• Person->BmCoverage(s)
• Person->BmSecurity(s) of an individual.

What I think is the major problem here is linking the BmSecurity with BmCoverage as apparently there's no coverage_id per BmSecurity but rather, a composite mapping through firmId and securityId.

In this case, Laravel does not officially support composite keys unfortunately, although you could use a trait like this... but you could also achieve the same with some tricky hasMany.

i.e. on BmCoverage

$this->hasMany('BmSecurity', 'securityId', 'securityId')
     ->andWhere('firmId', '=', $this->firmId);

Same applies for BmSecurity from BmPerson using HasManyThrough.

Hope that helps.

Answer 4

read laravel hasManyThrough relationship . it will help you to write this query more easily

https://laravel.com/docs/5.1/eloquent-relationships#has-many-through

Answer 5

I know that is a really old question. But i got this need on a custumer's project and i solved it using kind of like where.

$this->hasMany('BmCoverage', 'firmId', 'firmId', 'BmPerson')
 ->whereColumn('BmPerson.personId', '=', 'BmCoverage.personId');

I really expect that this answer helps someone.