skeith22's avatar

I wanted to query nested data until level 2 on a single model

Guys I have this model and I wanted to get the level 2 data, BUT this says relationship doesn't exist.

phpnamespace App\Models;

use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\HasManyThrough;

class Agent extends Model { protected $table = 'agents';

/**
 * The attributes that are mass assignable.
 *
 * @var list<string>
 */
protected $fillable = [
    'parent_agent_id',
    ...
];

public function lowerLevelTwoAgents(): HasManyThrough
{
    $relation = $this->hasManyThrough(
        static::class,
        static::class,
        'parent_agent_id',
        'parent_agent_id',
        'id',
        'id'
    );

    $relation->getThroughParent()->setTable('agents as child_agents');

    return $relation;
}

}

0 likes
6 replies
LaryAI's avatar
LaryAI
AI
17 hours ago
Level 58

Certainly! The issue here is that you want to get the grandchildren (level 2 descendants) of an agent via Eloquent. This is a self-referencing relationship, but hasManyThrough expects an intermediate model that is not the same as the end model (here they're both Agent). That can cause issues, especially if you try to alias tables.

You don't need to use getThroughParent() or manually set the table alias for a simple relationship. Let's clarify how to fetch children and grandchildren (level 1 and level 2) using Eloquent relationships.

Correct Setup

Assuming your table has these fields: id, parent_id, name, etc.

1. Define children relationship

public function children()
{
    return $this->hasMany(static::class, 'parent_id');
}

2. Define grandchildren (level 2) relationship

There is no built-in hasManyThrough solution for self-referencing models that resolves table aliasing automatically. However, you can get grandchildren via children:

public function grandchildren()
{
    return $this->hasManyThrough(
        static::class, // Final model
        static::class, // Intermediate model
        'parent_id',  // Foreign key on intermediate (children) table
        'parent_id',  // Foreign key on final table (grandchildren)
        'id',         // Local key on this model (Agent)
        'id'          // Local key on intermediate (children) model
    );
}

Now, accessing level 2 agents for a specific agent:

$agent = Agent::find(1);
$level2Agents = $agent->grandchildren;

Improved Approach: Use Eager Loading

If you want to get data for level 2 in a tree structure:

$agentWithDescendants = Agent::with('children.children')->find(1);

// Access children (level 1)
$children = $agentWithDescendants->children;

// Access grandchildren (level 2)
foreach ($children as $child) {
    $grandchildren = $child->children;
    // Do something with $grandchildren
}

In Summary

If you want a flat list of level 2 agents:

public function grandchildren()
{
    return $this->hasManyThrough(
        static::class,
        static::class,
        'parent_id',
        'parent_id',
        'id',
        'id'
    );
}

If you want a nested structure, use:

public function children()
{
    return $this->hasMany(static::class, 'parent_id');
}

$agentWithDescendants = Agent::with('children.children')->find(1);

Note

  • You don't need getThroughParent()->setTable('agents as child_agents'). That is unnecessary and may cause errors.
  • Make sure your foreign keys (parent_id) and primary keys (id) are named as expected.

Ask if you need the query for deeper levels or a recursive solution!

Glukinho's avatar

Give your data structure (tables, columns) and actual code that "says relationship doesn't exist".

skeith22's avatar

Even having this is just 1064 error, which I think is close enough to solving this issue.

ChildAgent::class is just an Alias Model that extends the original Agent Class`

<?php

namespace App\Models;

class ChildAgent extends Agent
{
    /**
     * Child agents table.
     *
     * @var string
     */
    protected $table = 'agents as child_agents';
}


public function lowerLevelTwoAgents(): HasManyThrough
    {
        return $this->hasManyThrough(
            static::class,
            ChildAgent::class,
            'parent_agent_id',
            'parent_agent_id',
            'id',
            'id'
        );
    }

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `child_agents.id` = `agents`.`parent_agent_id` where `agents` as `child_agent' at line 1 (Connection: mysql, Host: 127.0.0.1, Port: 3306, Database: medifix, SQL: select `agents`.*, `agents` as `child_agents.id` from `agents` inner join `agents` as `child_agents` on `agents` as `child_agents.id` = `agents`.`parent_agent_id` where `agents` as `child_agents.id` in (1) and `agents`.`deleted_at` is null and `agents` as `child_agents.deleted_at` is null)
skeith22's avatar
skeith22
OP
1 hour ago
Level 21

The issue here is at the on agentsaschild_agents.id=agents.parent_agent_idwhereagentsaschild_agents.id in (1)

where if you assign an alias name to the ChildAgent Class

protected $table = 'agents as child_agents';

it creates this query

SELECT
    `agents`.*,
    `agents` AS `child_agents.parent_agent_id`
FROM
    `agents`
INNER JOIN `agents` AS `child_agents`
ON
    `agents` AS `child_agents.id` = `agents`.`parent_agent_id`
WHERE
    `agents` AS `child_agents.parent_agent_id` IN(1) AND `agents`.`deleted_at` IS NULL AND `agents` AS `child_agents.deleted_at` IS NULL)

INSTEAD of this correct query

SELECT
    `agents`.*,
    `child_agents`.`parent_agent_id`
FROM
    `agents`
INNER JOIN `agents` AS `child_agents`
ON
    `child_agents`.`id` = `agents`.`parent_agent_id`
WHERE
    `child_agents`.`parent_agent_id` IN(1) AND `agents`.`deleted_at` IS NULL AND `child_agents`.`deleted_at` IS NULL;
skeith22's avatar

@glukinho apparently getThroughParent() is the one causing the relationship doesn't exist issue, doing other approach is just having MySQL 1064 or 1066 issues.

I've updated the Posts to reflect the actual code.

Having only this

    public function lowerLevelTwoAgents(): HasManyThrough
    {
        return $this->hasManyThrough(
            static::class,
            static::class,
            'parent_agent_id',
            'parent_agent_id',
            'id',
            'id'
        );
    }

Results in SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'agents'

Please or to participate in this conversation.