Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel fetch only pivot columns in many to many relationship

I have a User model that relates to a Section model through a pivot model UserTrainingSection. I have a pivot table that stores the foreign keys for both tables called section_user.

I have 2 additional pivot table columns called completed and completed_date.

The problem I am having is that when I fetch my data it returns all the columns from the User model along with the additional pivot columns.

    class Section extends Model
    {
        public $table = "sections";

        public $fillable = [
            'id',
            'name',
            'description',
            'parent',
            'position',
            'completion_percentage'
        ];

        public function users()
        {
            return $this->belongsToMany(User::class, 'section_user')->using('App\Models\UserTrainingSection')->withPivot('completed', 'completed_date');
        }
    }

In my API service I fetch my data like this:

Section::with(['users' => function ($q) {
                $q->where('users.id', Auth::user()->id);
            }])->first();

How do I only return the pivot table columns and exclude the columns from the user table?

At the moment it returns something like this:

"sections": [
                {
                    "id": 2,
                    "name": "Subsection 1 training",
                    "description": null,
                    "parent": 1,
                    "position": 2,
                    "completion_percentage": null,
                    "created_at": "2018-05-04 09:54:09",
                    "updated_at": "2018-05-11 09:14:59",
                    "users": [
                        {
                            "id": 1,
                            "name": "Test",
                            "email": "[email protected]",
                            "created_at": "12-04-2018 14:51:42",
                            "updated_at": "2018-04-19 14:14:36",
                            "pivot": {
                                "section_id": 2,
                                "user_id": 1,
                                "completed": 1,
                                "completed_date": "31/05/2018",
                                "expires": "31/05/2019"
                            },
                        }
                    ]
                }
            ]

What I would like to return is something like this:

"sections": [
                    {
                        "id": 2,
                        "name": "Subsection 1 training",
                        "description": null,
                        "parent": 1,
                        "position": 2,
                        "completion_percentage": null,
                        "created_at": "2018-05-04 09:54:09",
                        "updated_at": "2018-05-11 09:14:59",
                        "users": [
                            {
                                "pivot": {
                                    "section_id": 2,
                                    "user_id": 1,
                                    "completed": 1,
                                    "completed_date": "31/05/2018",
                                    "expires": "31/05/2019"
                                }
                            }
                        ]
                    }
                ]

So I basically get rid of the user data I don't want and only return the pivot data.

like image 920
user3574492 Avatar asked May 31 '18 09:05

user3574492


1 Answers

As per your expected output i guess you need data from junction model only, If that is the case i suggest you to define direct mapping of Section and UserTrainingSection

class Section extends Model
{

    public function training_users()
    {
        return $this->hasMany('App\Models\UserTrainingSection', 'section_id');
    }
    public function users()
    {
        return $this->belongsToMany(User::class, 'section_user')->using('App\Models\UserTrainingSection')->withPivot('completed', 'completed_date');
    }
}

In query you can simply do

Section::with('training_users')->first();
like image 194
M Khalid Junaid Avatar answered Oct 26 '22 22:10

M Khalid Junaid