Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

huseyinalperenn's avatar

laravel relationship 4 tables

Hi friends. I have 4 tables, I will list them below. I want to associate these tables with laravel's relationship system.

/discuss/channels/eloquent/4-tables-relationship

I read the topic here, I wanted to do something exactly like this, I did it and it works, but there is a very minor problem.

First of all, I am sharing my paintings.

->admin_users
   ->id
   ->username

->permission_category
   ->id
   ->name

->permissions
   ->id
   ->permission_category_id
   ->name

->admin_perms
   ->id
   ->admin_users_id (foreign key)
   ->permissions_id (foreign key)

I also share my models and resources:

PermissionsResource.php

return [
            "id" => $this->category[0]->id,
            "name" => $this->category[0]->name,
            "permissions" => [
                "id" => $this->id,
                "name" => $this->name
            ],
        ];

AdminUsers.php (Model)

    public function permissions() {
        return $this->belongsToMany(Permissions::class, 'admin_perms');
    }

Permissions.php (Model)

    public function category() {
        return $this->belongsToMany(PermissionCategory::class, 'permissions', 'id');
    }

and finally i am sharing my controller:

UsersController.php

public function add() {
        $data = array();
        $user = AdminUsers::where('id', $this->uid())->first();
        if($user->hasAdministrator()->first())
            $data['permission_category'] = PermissionCategory::with('permissions')->get();
        else
            $data['permission_category'] = PermissionsResource::collection($user->permissions()->with('category')->get());
        return $data;
        die();
        return view('admin.users.add', $data);
    }

and I get an output like this:

"permission_category": [{
		"id": 2,
		"name": "user",
		"permissions": {
			"id": 2,
			"name": "userCreate"
		}
	},
	{
		"id": 2,
		"name": "user",
		"permissions": {
			"id": 3,
			"name": "userUpdate"
		}
	}
]

I want to get output like below.

"permission_category": [{
		"id": 2,
		"name": "user",
		"permissions": [
            {
			  "id": 2,
			  "name": "userCreate"
            },
            {
			  "id": 3,
			  "name": "userUpdate"
            },
		]
	}
]

Actually, I translated what I want into a sql query.

SELECT 
    admin_perms.admin_users_id as user_id,
    permission_category.name as category_name,
    permissions.id as permission_id,
    permissions.name as permission_name
FROM 
    permissions
JOIN permission_category ON permissions.permission_category_id = permission_category.id
JOIN admin_perms ON permissions.id = admin_perms.permissions_id
JOIN admin_users ON admin_perms.admin_users_id = admin_users.id
WHERE admin_users.id = 6

and this query gave me the following output:

[
	{
		user_id: 6,
		category_name: 'user',
		permission_id: 2,
		permission_name: 'userCreate'
	},
	{
		user_id: 6,
		category_name: 'user',
		permission_id: 3,
		permission_name: 'userUpdate'
	}
]

This is exactly the query I want. But I want to do this with eloquent relationship. Can you help me?

0 likes
17 replies
SilenceBringer's avatar

@huseyinalperenn the first part is not in english - little hard to understand

Then you give 2 section:

I want to get output like below.

"permission_category": [{
		"id": 2,
		"name": "user",
		"permissions": [
            {
			  "id": 2,
			  "name": "userCreate"
            },
            {
			  "id": 3,
			  "name": "userUpdate"
            },
		]
	}
]

and

This is exactly the query I want

[
	{
		user_id: 6,
		category_name: 'user',
		permission_id: 2,
		permission_name: 'userCreate'
	},
	{
		user_id: 6,
		category_name: 'user',
		permission_id: 3,
		permission_name: 'userUpdate'
	}
]

the output is different. Which one exactly do you want?

1 like
huseyinalperenn's avatar

@SilenceBringer I want to get exactly the above output.

so:

"permission_category": [{
		"id": 2,
		"name": "user",
		"permissions": [
            {
			  "id": 2,
			  "name": "userCreate"
            },
            {
			  "id": 3,
			  "name": "userUpdate"
            },
		]
	}
]
SilenceBringer's avatar

@huseyinalperenn oh, ok

PermissionCategory::with(['permissions' => fn ($query) =>
		$query->whereHas('users', fn ($query) => $query->where('id', $user->id))
			->orWhereHas('adminUsers', fn ($query) => $query->where('id', $user->id))
	])
	->get()

assuming you have defined users and adminUsers realtionships on Permission model

1 like
SilenceBringer's avatar
SilenceBringer
3 years ago
Best Answer
Level 55

@huseyinalperenn on AdminUsers.php (Model) you have

    public function permissions() {
        return $this->belongsToMany(Permissions::class, 'admin_perms');
    }

but the same time for Permission model

    public function adminUsers() {
        return $this->belongsToMany(AdminUsers::class, 'admin_perms');
    }

and - fixed version:

PermissionCategory::with(['permissions' => fn ($query) =>
		$query->whereHas('adminUsers', fn ($query) => $query->where('id', $user->id))
	])
	->get();
1 like
huseyinalperenn's avatar

@SilenceBringer oh yes that was the best answer for me. Thank you so much. However, in the incoming categories, the permissions are empty, is there a way to filter them, so it doesn't show all the categories?

[{
		"id": 1,
		"name": "general",
		"status": true,
		"created_at": null,
		"updated_at": null,
		"deleted_at": null,
		"permissions": []
	},
	{
		"id": 2,
		"name": "user",
		"status": true,
		"created_at": null,
		"updated_at": null,
		"deleted_at": null,
		"permissions": [{
				"id": 2,
				"permission_category_id": 2,
				"name": "userCreate",
				"status": true,
				"created_at": null,
				"updated_at": null,
				"deleted_at": null
			},
			{
				"id": 3,
				"permission_category_id": 2,
				"name": "userUpdate",
				"status": true,
				"created_at": null,
				"updated_at": null,
				"deleted_at": null
			}
		]
	},
	{
		"id": 3,
		"name": "company",
		"status": true,
		"created_at": null,
		"updated_at": null,
		"deleted_at": null,
		"permissions": []
	}
]
SilenceBringer's avatar

@huseyinalperenn sure

PermissionCategory::withWhereHas('permissions', fn ($query) =>
		$query->whereHas('adminUsers', fn ($query) => $query->where('id', $user->id))
	)
	->get();

or if you have little outdated version

PermissionCategory::whereHas('permissions', fn ($query) =>
		$query->whereHas('adminUsers', fn ($query) => $query->where('id', $user->id))
	)
	->with(['permissions' => fn ($query) =>
		$query->whereHas('adminUsers', fn ($query) => $query->where('id', $user->id))
	])
	->get();
1 like
huseyinalperenn's avatar

@SilenceBringer while a user A is editing the permissions of user B, user A needs to see both the permissions it has and the permissions that user B has. For this, I thought of something like this and added something like this to the code you shared and it gives me a nice output yes!

PermissionCategoryResource::collection(PermissionCategory::with([
                'permissions' => fn ($query) => $query->whereHas('adminUsers', fn ($query) => $query->where('admin_users.id', $this->user()->id)),
                'selected' => fn ($query) => $query->whereHas('adminUsers', fn ($query) => $query->where('admin_users.id', $id)),
            ])
            ->select('id','name')
            ->get());

output:

{
	"id": 2,
	"name": "user",
	"permissions": {
		"permissions": [
			[{
					"id": 2,
					"name": "userCreate"
				},
				{
					"id": 3,
					"name": "userUpdate"
				},
				{
					"id": 4,
					"name": "userDelete"
				}
			]
		],
		"selected": [
			[{
					"id": 2,
					"name": "userCreate"
				},
				{
					"id": 3,
					"name": "userUpdate"
				}
			]
		]
	}
},

selected: The permissions of user B, edited by user A.

However, there is a situation like this. In order to compare the permissions in permissions with the permissions in selected, I need to put them both in a foreach loop. I don't like using a nested foreach loop. And I think Laravel has a solution for this. I'm new to Laravel and I'm trying to learn something so forgive me. Actually, I want an output like this. Let's say we loop the permissions in Permissions. Inside the loop: Does the permission in Permissions also exist in selected ? If it exists, I need to give the selected: true key and value to its permission in Permissions. So, to explain briefly, it is as follows:

{
	"id": 2,
	"name": "user",
	"permissions": {
		"permissions": [
			[{
					"id": 2,
					"name": "userCreate"
					"selected":true
				},
				{
					"id": 3,
					"name": "userUpdate"
					"selected":true
				},
				{
					"id": 4,
					"name": "userDelete"
				}
			]
		],
		"selected": [
			[{
					"id": 2,
					"name": "userCreate"
				},
				{
					"id": 3,
					"name": "userUpdate"
				}
			]
		]
	}
},

yes, it is better to explain with this example.

I tried this with resources and array map but failed. Do you have a solution suggestion for this issue?

Please or to participate in this conversation.