How to Access and Manipulate Pivot Table

I have two tables, users and like_categories which has many-to-many relationship. The pivot table is called like_category_user. After inserting two users data into the db, here is my pivot table look like: https://i.imgur.com/MeeRbiV.png .

Im a little bit confused on how can i access the pivot table since I didnt create a custom model for that pivot table. I want to count the amount for each of the different like category for each user and store it in object array like this:

[
    {
        "User Id": 1,
        "Like Categories": [
            {
                "Category": Chinese Restaurant
                "Amount": 1
            },
            {
                "Category": Korean Restaurant
                "Amount": 2
            },
            {
                "Category": Fast Food Restaurant
                "Amount": 3
            },
            {
                "Category": Italian Restaurant
                "Amount": 1
            },
            {
                "Category": Steakhouse Restaurant
                "Amount": 3
            }
        ]
    },
    {
        "User Id": 2,
        "Like Categories": [
            {
                "Category": Thai Restaurant
                "Amount": 1
            },
            {
                "Category": Kebab Shop
                "Amount": 3
            },
            {
                "Category": Pizza Place
                "Amount": 2
            },
            {
                "Category": Steakhouse
                "Amount": 1
            }
        }
    }
]

My process method:

public function showUserLikesData() {
    //
}

bobbybouwmann

6 years ago

Level 88

You can access them through the pivot property of the given model

$users = User::with('like_categories')->get();

foreach ($users as $user) {
    echo $user->pivot->category_name;
}

Updating them works a bit different. You can find good examples for that in the docs: https://laravel.com/docs/6.x/eloquent-relationships#updating-many-to-many-relationships

honeyBear

6 years ago

Level 3

Hey man, i tried doing it like this:

public function showUserLikesData() {
        
        $users = User::all();

        $counter = 0;
        $countUser = 0;
        $countThatCategory = 0;
        $categoryName = '';

        foreach($users as $user) {

            $userLikesData[$countUser]['User Id'] = $user->id;

            foreach ($user->likeCategories as $likeCategory) {

                 $categoryName = $likeCategory->pivot->category_name;
                    
                foreach ($user->likeCategories as $likeCategory) {
                    $checkCategoryName = $likeCategory->pivot->category_name;
    
                    if ($categoryName == $checkCategoryName) {
                           $countThatCategory++;
                    }
                }
        
                $userLikesData[$countUser]['Like Categories'][$counter]['Category'] = $categoryName;
                $userLikesData[$countUser]['Like Categories'][$counter]['Amount'] = $countThatCategory;

                $countThatCategory = 0;
                    
                $counter++;
            }
            $countUser++;
            $counter=0;
        }

        return $userLikesData;
    }

But it returns this output:

[
    {
        "User Id": 1,
        "Like Categories": [
            {
                "Category": "Chinese Restaurant",
                "Amount": 1
            },
            {
                "Category": "Korean Restaurant",
                "Amount": 2
            },
            {
                "Category": "Korean Restaurant",
                "Amount": 2
            },
            {
                "Category": "Fast Food Restaurant",
                "Amount": 3
            },
            {
                "Category": "Fast Food Restaurant",
                "Amount": 3
            },
            {
                "Category": "Fast Food Restaurant",
                "Amount": 3
            },
            {
                "Category": "Italian Restaurant",
                "Amount": 1
            },
            {
                "Category": "Steakhouse",
                "Amount": 3
            },
            {
                "Category": "Steakhouse",
                "Amount": 3
            },
            {
                "Category": "Steakhouse",
                "Amount": 3
            }
        ]
    },
    {
        "User Id": 2,
        "Like Categories": [
            {
                "Category": "Thai Restaurant",
                "Amount": 1
            },
            {
                "Category": "Kebab Shop",
                "Amount": 3
            },
            {
                "Category": "Kebab Shop",
                "Amount": 3
            },
            {
                "Category": "Kebab Shop",
                "Amount": 3
            },
            {
                "Category": "Pizza Place",
                "Amount": 2
            },
            {
                "Category": "Pizza Place",
                "Amount": 2
            },
            {
                "Category": "Steakhouse",
                "Amount": 1
            }
        ]
    }
]

sairum

6 years ago

Level 1


User::select('id')->with('like_categories')->get();

with this query you will get the same result you want

Please or to participate in this conversation.