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

  eloquent, eloquent--relationship, laravel, php

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('AppModelsV1Service', 'user_services', 'user_id', 'service_id');
    }

    public function categories()
    {
        return $this->belongsToMany('AppModelsV1Category', '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": "[email protected]",
             "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": "[email protected]",
            "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
                },
            ]
        }
]

Source: Laravel

Leave a Reply