Laravel: How do i return data combinely when one table is related to more than one table?

Question

So, in my application I have models: user, category, service(i.e subcategory) and userService.

There exists many to many relationship between user and category, user and service. And one to many between category and service.

// Tables

Users        : id, name, email, password
Categories   : id, name
Services     : id, name, category_id
user_services: id, user_id, category_id, service_id

Relationship is defined in model as:

// User Model

    public function services()
    {
        return $this->belongsToMany('App\Models\V1\Service', 'user_services', 'user_id', 'service_id');
    }

    public function categories()
    {
        return $this->belongsToMany('App\Models\V1\Category', 'user_services', 'user_id', 'category_id');
    }

// category model

 public function services()
    {
        return $this->hasMany(Service::class);
    }

After populating my db with some random data, the user_services table look like this:

// user_services table

id   user_id   category_id  service_id 
1    1         1            1
2    1         1            2
3    2         1            1

so far code is good but when i want particular user with services and categories, i am not able to get it as i expected.

The eloquent method that used to fetch user with services and categories:

public function userWithCategoryServices(Request $request)
{
  return User::where('id', $request->id)->with(categories.services)->get();
}

The problem with this is:

1. As there is case one user with one category can have multiple services, so this eloquent method will return same category multiple times. But i want all services belonging to one category grouped in single category key for that particular user.

2. Another problem, instead of fetching services of that particular user only,but it will fetch all services from db for users particular category.

           [
           {
            "id": 1,
            "name": "Ashish Bogati",
            "email": "abbaasdaashisdh23@gmail.com",
            "categories": [
            {
            "id": 1,
            "name": "IT",
            "pivot": {
                      "user_id": 1,
                      "category_id": 1
                     },
            "services": [
                {
                    "id": 1,
                    "name": "Web Developer",
                    "category_id": 1, 
                },
                {
                    "id": 2,
                    "name": "Web Design",
                    "category_id": 1
                },
                {
                    "id": 3,
                    "name": "Database Design",
                    "category_id": 1
                }
            ]
        },
        {
            "id": 1,
            "name": "IT",
            "pivot": {
                      "user_id": 1,
                      "category_id": 1
                     },
            "services": {
                    "id": 1,
                    "name": "Web Developer",
                    "category_id": 1
                },
                {
                    "id": 2,
                    "name": "Web Design",
                    "category_id": 1
                },
                {
                    "id": 3,
                    "name": "Database Design",
                    "category_id": 1
                }
            ]
        }
    ]
   

   } ]

As in this response, category name IT is repeated twice for user and all services are fetched for that category instead of for particular user only.

Response i wanted:

           [
           {
            "id": 1,
            "name": "Ashish Bogati",
            "email": "abbaasdaashisdh23@gmail.com",
            "categories": [
            {
            "id": 1,
            "name": "IT",
            "pivot": {
                      "user_id": 1,
                      "category_id": 1
                     },
            "services": [
                {
                    "id": 1,
                    "name": "Web Developer",
                    "category_id": 1, 
                },
                {
                    "id": 2,
                    "name": "Web Design",
                    "category_id": 1
                },
            ]
        }
]

Answer 1

the database design is a little strange.we meet duplicate category_id in user_services.but that's is not the problem

//in service model
public function category()
{
  return $this->hasOne(Category::class);
}

public function userWithCategoryServices(Request $request)
{
  $services = User::where('id', $request->id)->with("services.category")->get();
  $servicesGrouped = $services['services']->groupBy('category_id');
}

the services will be right.there will be no more unwanted data.
this is so far we can do by Laravel eloquent.
because the relation map is user->services->category.not user->categories->services

---

use php to build the structure of the wanted response.
But as Restful principle.you should make api pure.which means one api give only one type of entity.client will be more clear with the rest api and deal with complex structure themself.

public function userWithCategoryServices(Request $request)
{
  $services = User::where('id', $request->id)->with("services.category")->get()->toArray();
  $categories = []
  for($services as $service){
      if(!isset($categories[$service['category_id']])){
        $categories[$service['category_id']] = $service['category'];
        $categories[$service['category_id']]['services'] = [];
      }
      unset($service['category']);
      $categories[$service['category_id']]['services'][] = $service;
  }
  $categories = array_values($categories);
  unset($services['services']);
  $services['categories'] = $categories;
  return $services;
}