BelongsTo Model Through Recursive not working

[aldesrahim]

Hi, i have this schema

cashbanks:
- id
- coa_id
- name

coas:
- id
- parent_id -> nullable
- name

and i have this data

1. coa parent (id: 1, name: parent)
2. coa child (id: 2, name: child, parent_id: 1)
3. cashbank (id:1, coa_id: 2, name: bank)

basically i want to eagerLoad "coa parent" when retrieving "cashbank", so i've figured this out using HasOneThrough, but the code looks bit messy, and i'm trying to use this package (if possible)

this the code i implemented using this package

return $this->belongsToThrough(
    Coa::class,
    Coa::class . ' as coa_sibling',
    foreignKeyLookup: [Coa::class => 'parent_id'],
);

and this the code i implemented using HasOneThrough

$coaTable = (new Coa())->getTable();
$query = $this
    ->newRelatedInstance(Coa::class)
    ->newQuery()
    ->fromRaw("{$coaTable} AS coa_parent");

$parentModel = $this->newRelatedInstance(Coa::class);

/**
 * Has one COA parent through direct Cashbank's COA (reverse belongsTo)
 */
return $this->newHasOneThrough(
    query: $query,
    farParent: $this,
    throughParent: $parentModel,
    firstKey: 'id', // from coas table (reverse belongsTo)
    secondKey: 'parent_id', // from coas table
    localKey: 'coa_id', // from self table (reverse belongsTo)
    secondLocalKey: 'coa_parent.id'
)->selectRaw('coa_parent.*'); // only select coa_parent

[aldesrahim]

for additional information:

this is the query that generated using HasOneThrough:

select coa_parent.*, `coas`.`id` as `laravel_through_key` from coas AS coa_parent inner join `coas` on `coa_parent`.`id` = `coas`.`parent_id` where `coas`.`id` in ('9be3cb15-c23b-4bfe-80f3-3f9f02714c2e')```

and this is the query that generated using this package:

select `coas`.*, `coa_sibling`.`id` as `laravel_through_key` from `coas` inner join `coas` as `coa_sibling` on `coa_sibling`.`parent_id` = `coas`.`id` where `coa_sibling`.`id` in (null)

[staudenmeir]

Hi @aldesrahim, The package doesn't support this particular case.

Use one of my other packages instead: https://github.com/staudenmeir/eloquent-has-many-deep

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

    public function coa()
    {
        return $this->hasOneDeep(Coa::class, [Coa::class . ' as alias'], ['id', 'id'], ['coa_id', 'parent_id']);
    }
}

[aldesrahim]

Thank you, it is working now