Laravel hasManyThrough relationship with non-default local key

Question

My schema is as follows:

Clients (hasMany Accounts)

• id
• name

Accounts (hasMany Holdings, belongsTo Clients)

• id (int)
• account_id (string, unique key)
• name

Holdings (belongsTo Accounts)

• id
• account_id (string)
• value
• holding_date... etc

So, Client hasMany Accounts hasMany Holdings. The caveat being that the local key for accounts is account_id, not just id as is expected. This is because there is a requirement for the accounts to have a string identifier. In the holdings table the foreign key is also account_id.

I have defined my relationships like so:

// Client.php
public function accounts()
{
    return $this->hasMany('Account');
}

// Account.php
public function client()
{
    return $this->belongsTo('Client');
}
public function holdings()
{
    return $this->hasMany('Holding');
}

// Holding.php
public function account()
{
    return $this->belongsTo('Account', 'account_id', 'account_id');
}

If I wanted to query all the holdings for a given client ID how would I do this? If I do something like

Client::find($id)->accounts->holdings;

I get this error:

Undefined property: Illuminate\Database\Eloquent\Relations\HasMany::$holdings

I also tried using the hasManyThrough relationship (having added the relationship to my model) but there seems to only be a way to define the foreign key, not the local key for the accounts. Any suggestions?

Answer 1

You'd have to override Eloquent a little bit. I just ran into something very similar with a BelongsToMany relationship. I was trying to perform a many-to-many query where the relevant local-key was not the primary key. So I extended Eloquent's BelongsToMany a little bit. Start by building an override class for the BelongsToMany relationship class:

namespace App\Overrides\Relations;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany as BaseBelongsToMany;

class BelongsToMany extends BaseBelongsToMany
{
    protected $localKey;

    /**
     * @var array
     */
    protected $customConstraints = [];

    /**
     * BelongsToMany constructor.
     * @param Builder $query
     * @param Model $parent
     * @param string $table
     * @param string $foreignKey
     * @param string $otherKey
     * @param string $relationName
     * @param string $localKey
     */
    public function __construct(
        Builder $query,
        Model $parent,
        $table,
        $foreignKey,
        $otherKey,
        $relationName = null,
        $localKey = null
    ) {
        //The local-key binding, assumed by Eloquent to be the primary key of the model, will have already been set
        if ($localKey) {    //If it's intended to be overridden, that value in the Query/Builder object needs updating
            $this->localKey = $localKey;
            $this->setLocalKey($query, $parent, $table, $foreignKey, $localKey);
        }
        parent::__construct($query, $parent, $table, $foreignKey, $otherKey, $relationName);
    }

    /**
     * If a custom local-key field is defined, don't automatically assume the pivot table's foreign relationship is
     * joined to the model's primary key.  This method is necessary for lazy-loading.
     *
     * @param Builder $query
     * @param Model $parent
     * @param string $table
     * @param string $foreignKey
     * @param string $localKey
     */
    public function setLocalKey(Builder $query, Model $parent, $table, $foreignKey, $localKey)
    {
        $qualifiedForeignKey = "$table.$foreignKey";
        $bindingIndex = null;

        //Search for the 'where' value currently linking the pivot table's foreign key to the model's primary key value
        $query->getQuery()->wheres = collect($query->getQuery()->wheres)->map(function ($where, $index) use (
            $qualifiedForeignKey,
            $parent,
            &$bindingIndex
        ) {
            //Update the key value, and note the index so the corresponding binding can also be updated
            if (array_get($where, 'column', '') == $qualifiedForeignKey) {
                $where['value'] = $this->getKey($parent);
                $bindingIndex = $index;
            }
            return $where;
        })->toArray();

        //If a binding index was discovered, updated it to reflect the value of the custom-defined local key
        if (!is_null($bindingIndex)) {
            $bindgings = $query->getQuery()->getBindings();
            $bindgings[$bindingIndex] = $this->getKey($parent);
            $query->getQuery()->setBindings($bindgings);
        }
    }

    /**
     * Get all of the primary keys for an array of models.
     * Overridden so that the call to $value->getKey() is replaced with $this->getKey()
     *
     * @param  array   $models
     * @param  string  $key
     * @return array
     */
    protected function getKeys(array $models, $key = null)
    {
        if ($key) {
            return parent::getKeys($models, $key);
        }
        return array_unique(array_values(array_map(function ($value) use ($key) {
            return $this->getKey($value);
        }, $models)));
    }

    /**
     * If a custom local-key field is defined, don't automatically assume the pivot table's foreign relationship is
     * joined to the model's primary key.  This method is necessary for eager-loading.
     *
     * @param Model $model
     * @return mixed
     */
    protected function getKey(Model $model)
    {
        return $this->localKey ? $model->getAttribute($this->localKey) : $model->getKey();
    }

    /**
     * Set the where clause for the relation query.
     * Overridden so that the call to $this->parent->getKey() is replaced with $this->getKey()
     * This method is not necessary if this class is accessed through the typical flow of a Model::belongsToMany() call.
     * It is necessary if it's instantiated directly.
     *
     * @return $this
     */
    protected function setWhere()
    {
        $foreign = $this->getForeignKey();
        $this->query->where($foreign, '=', $this->getKey($this->parent));
        return $this;
    }
}

Next, you'll need to make the Model class actually use it:

namespace App\Overrides\Traits;

use App\Overrides\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\Relation;

/**
 * Intended for use inside classes that extend Illuminate\Database\Eloquent\Model
 *
 * Class RelationConditions
 * @package App\Overrides\Traits
 */
trait CustomConstraints
{
    /**
     * Intercept the Eloquent Model method and return a custom relation object instead
     *
     * {@inheritdoc}
     */
    public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null, $localKey = null)
    {
        //Avoid having to reproduce parent logic here by asking the returned object for its original parameter values
        $base = parent::belongsToMany($related, $table, $foreignKey, $otherKey, $relation);
        //The base action will have already applied the appropriate constraints, so don't re-add them here
        return Relation::noConstraints(function () use ($base, $localKey) {
            //These methods do the same thing, but got renamed
            $foreignKeyName = version_compare(app()->version(), '5.3', '>=')
                ? $base->getQualifiedForeignKeyName()
                : $base->getForeignKey();

            $relatedKeyName = version_compare(app()->version(), '5.3', '>=')
                ? $base->getQualifiedRelatedKeyName()
                : $base->getOtherKey();

            return new BelongsToMany(
                $base->getQuery(),
                $base->getParent(),
                $base->getTable(),
                last(explode('.', $foreignKeyName)),
                last(explode('.', $relatedKeyName)),
                $base->getRelationName(),
                $localKey
            );
        });
    }
}

Use this trait inside your model class, and you now have the ability to add a 6th argument that specifies what local-key to use, rather than automatically assume the primary one.

Answer 2

I think you can use load method to get the corresponding resulting query for each accounts. Something like:

Client::find($id)->load('accounts.holdings');

This means that client_id is present in accounts and holdings has account_id as well.

PS: I am not super sure how this would work in this context. But I hope this can lead you to find the way to do it.

Answer 3

Assuming you have client_id on accounts table,

do this:

// Account model
public function holdings()
{  
  return $this->hasMany('Holding', 'account_id', 'account_id');
}

// then

$client = Client::with('accounts.holdings')->find($id);
$client->accounts // collection
          ->first() // or process the collecction in the loop
          ->holdings; // holdlings collection

HasManyThrough will work only if Account model has (or will have for that purpose) $primaryKey set to account_id instead of default id

---

Since account_id is not primary key of the Account model, you can't use hasManyThrough. So I suggest you do this:

$accountIds = $client->accounts()->lists('account_id');

//  if it was many-to-many you would need select clause as well:
//  $accountIds = $client->accounts()->select('accounts.account_id')->lists('account_id');

$holdings = Holding::whereIn('account_id', $accountIds)->get();

This way you get the Collection just like you wanted, donwside is 1 more query needed in comparison to eager loading.

Answer 4

You need to change your relation in Account model

// Account.php
public function client()
{
   return $this->belongsTo('Client','account_id');
}

But, it more appropriate to change column name to client_id in Accounts table