Unable to get login user data from "users" table.

[msayubi76]

Login user can create multiple groups and add multiple user to each group. I want to display all unique users added to all groups. So far I tried Belongs to Trhough and Eloquent has many deep but unable to resolve my issue. Anyone help

User Model

class User extends Authenticatable implements MustVerifyEmail
{
 use \Znck\Eloquent\Traits\BelongsToThrough; //extra package added
 public function companies()
    {
        return $this->belongsToThrough(
            User::class,
            [group::class, GroupUser::class],
            null, 
            '', 
            [Group::class => 'assigned_user_id']
        );
    }
}

Group Model

class group extends Model
{

}

GroupUser Model for intermediate table

class GroupUser extends Model
{
    protected $table = 'group_user';
}

Fetch myCompanies

$user = auth()->user();
$user->myCompanies;

Exception Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'group_user.id' in 'where clause' (SQL: select users.* from users inner join groups on groups.user_id = users.id inner join group_user on group_user.assigned_user_id = groups.id where group_user.id is null limit 1)

Users Table Structure

Groups Table Structure

GroupUser Table Structure

[staudenmeir]

Please share the structure of all three tables.

[msayubi76]

I jsut updated the question. Please have a look. @staudenmeir

[staudenmeir]

Is groups.assigned_user_id connected to users.assigned_user_id or users.id?

[msayubi76]

Is groups.assigned_user_id connected to users.assigned_user_id or users.id?

@staudenmeir it's connected with users.id

[staudenmeir]

Use the eloquent-has-many-deep package:

class User extends Authenticatable implements MustVerifyEmail
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function companies()
    {
        return $this->hasManyDeep(
            User::class,
            ['group_user', Group::class],
            [null, null, 'id'],
            [null, null, 'assigned_user_id']
        );
    }
}

Use this relationship to get unique users:

class User extends Authenticatable implements MustVerifyEmail
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function companies()
    {
        return $this->hasManyDeep(
            User::class,
            ['group_user', Group::class],
            [null, null, 'id'],
            [null, null, 'assigned_user_id']
        )->distinct();
    }
}

[msayubi76]

Thanks @staudenmeir a little close. I tried and results are empty. In fact records are present in DB. This was the query after distinctrelation.

SELECT DISTINCT `users`.*,
       `group_user`.`user_id` AS `laravel_through_key`
FROM   `users`
       INNER JOIN `groups`
               ON `groups`.`assigned_user_id` = `users`.`id`
       INNER JOIN `group_user`
               ON `group_user`.`group_id` = `groups`.`id`
WHERE  `group_user`.`user_id` = 43

But I need

SELECT DISTINCT `users`.*,
       `group_user`.`user_id` AS `laravel_through_key`
FROM   `users`
       INNER JOIN `groups`
               ON `groups`.`assigned_user_id` = `users`.`id`
       INNER JOIN `group_user`
               ON `group_user`.`group_id` = `groups`.`id`
WHERE  `groups`.`assigned_user_id` = 43

Could you please where should I make changes above mentioned distinct relation. .

[staudenmeir]

SELECT DISTINCT users.*, group_user.user_id AS laravel_through_key FROM users INNER JOIN groups ON groups.assigned_user_id = users.id INNER JOIN group_user ON group_user.group_id = groups.id WHERE groups.assigned_user_id = 43

Did you run this query in your database?

[msayubi76]

Yes and get the accurate results that are required but I don't know how can I make changes in relation

[staudenmeir]

I asked because the changed query no longer has the structure of a "normal" relationship query: Deep relationship queries are a chain of join clauses where every join table is used in two join/where constraints ("on both sides"). In your query however, group_user is only used once which doesn't fit into the whole structure of relationship queries.

Can you provide a small dataset and the expected result for me to reproduce to test both queries?

[msayubi76]

@staudenmeir I already shared all required tables and structure of data that is required. What else you need?. I'll provide you.

[msayubi76]

@staudenmeir I try to capture everything in single image. Hopefully this image will help you to understand my problem.

[msayubi76]

I just created a public repository for fresh new project. please have a look on it. Repository Link

[msayubi76]

@staudenmeir bro I'm still stuck in it. Would you please help ?

[staudenmeir]

How can I reproduce the issue with your repository? I saw that the relationships are generated randomly. How do I know what the expected result is?

[msayubi76]

How can I reproduce the issue with your repository? I saw that the relationships are generated randomly. How do I know what the expected result is?

You can clone this project on your PC. I want to fetch all unique users added to all groups of login users. I've mentioned required results in this issue as well.

[msayubi76]

How can I reproduce the issue with your repository? I saw that the relationships are generated randomly. How do I know what the expected result is?

Actually I added all my of code to public repository so that you can understand it clearly. Let me know if you didn't get anything.