Laravel Removing Pivot data in many to many relationship

Question

Not sure if I set this up correctly. In Laravel I'm creating two models with a many-to-may relationship

The models are Item and Tags. Each one contains a belongsTo to the other.

When I run a query like so:

Item::with('tags')->get();

It returns the collection of items, with each item containing a tags collection. However the each tag in the collection also contains pivot data which I don't need. Here it is in json format:

[{
    "id":"49",
    "slug":"test",
    "order":"0","tags":[
        {"id":"3","name":"Blah","pivot":{"item_id":"49","tag_id":"3"}},
        {"id":"13","name":"Moo","pivot":{"item_id":"49","tag_id":"13"}}
    ]
}]

Is there anyway to prevent this data from getting at

This is an obvious duplicate of http://stackoverflow.com/questions/20887530/laravel-4-1-remove-pivot-attributes-from-response - Please remove the bounty so we can close it as duplicate. — Sergiu Paraschiv, Jan 08 '15 at 11:36
This seems to only work when I do a `->toArray()` or `->toJson()` but if i just print out `$item->tags` I still see the pivot data in the object. Seems like it's doing unnecessary querying as I'm not trying to access any data from the pivot table. Still would like an answer. — Rob, Jan 08 '15 at 12:10
For instance if I were to write the query directly through the pivot: `SELECT t.* FROM tags AS t JOIN city_tag AS ct WHERE ct.item_id = 111;` — Rob, Jan 08 '15 at 12:14
No, that's the way the API is supposed to work. It does not waste that many more resources (except bandwidth for transmitting them from the DB maybe) by supplying the pivot data too. And I think you do understand that there's no other way of implementing a many-to-many relationship other than a _pivot table_. `$hidden` is an API _feature_ (of Eloquent) and as such is only available if you use _the API_ (`toArray`, `toJson`). A PHP dump of the objects will obviously present all the information within. There is no other _answer_ here. — Sergiu Paraschiv, Jan 08 '15 at 12:37
The query generated by Eloquent would be close to this one: `SELECT t.*, ct.* FROM tags AS t JOIN city_tag AS ct WHERE ct.item_id = 111;` - no real performance penalty here... — Sergiu Paraschiv, Jan 08 '15 at 12:43

score 17 · Answer 1 · edited Nov 09 '20 at 17:14

17

you can just add the name of the field in the hidden part in your model like this:

protected $hidden = ['pivot'];

that's it , it works fine with me.

edited Nov 09 '20 at 17:14

Christian Giupponi

7,408
11
68
113

answered Aug 22 '17 at 15:55

Aouidane Med Amine

1,571
16
17

this work on entire model, but how can I do for just one method? thnaks – RamiroRS Dec 07 '17 at 19:23

score 9 · Accepted Answer · edited May 23 '17 at 11:57

You have asked and you shall receive your answer. But first a few words to sum up the comment section. I personally don't know why you would want / need to do this. I understand if you want to hide it from the output but not selecting it from the DB really has no real benefit. Sure, less data will be transferred and the DB server has a tiny tiny bit less work to do, but you won't notice that in any way.

However it is possible. It's not very pretty though, since you have to override the belongsToMany class.

First, the new relation class:

class BelongsToManyPivotless extends BelongsToMany {
    /**
     * Hydrate the pivot table relationship on the models.
     *
     * @param  array  $models
     * @return void
     */
    protected function hydratePivotRelation(array $models)
    {
        // do nothing
    }

    /**
     * Get the pivot columns for the relation.
     *
     * @return array
     */
    protected function getAliasedPivotColumns()
    {
        return array();
    }
}

As you can see this class is overriding two methods. hydratePivotRelation would normally create the pivot model and fill it with data. getAliasedPivotColumns would return an array of all columns to select from the pivot table.

Now we need to get this integrated into our model. I suggest you use a BaseModel class for this but it also works in the model directly.

class BaseModel extends Eloquent {

    public function belongsToManyPivotless($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null){
        if (is_null($relation))
        {
            $relation = $this->getBelongsToManyCaller();
        }

        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $instance = new $related;

        $otherKey = $otherKey ?: $instance->getForeignKey();

        if (is_null($table))
        {
            $table = $this->joiningTable($related);
        }

        $query = $instance->newQuery();

        return new BelongsToManyPivotless($query, $this, $table, $foreignKey, $otherKey, $relation);
    }
}

I edited the comments out for brevity but otherwise the method is just like belongsToMany from Illuminate\Database\Eloquent\Model. Of course except the relation class that gets created. Here we use our own BelongsToManyPivotless.

And finally, this is how you use it:

class Item extends BaseModel {
    public function tags(){
        return $this->belongsToManyPivotless('Tag');
    }
}

but why all that ? you wan just this in your model : protected $hidden = ['pivot']; — Aouidane Med Amine, Aug 22 '17 at 15:53
I can give a case where it is needed : with Postgresql if the select contains a grouping operation (SUM, COUNT), it is required that all columns have a grouping operation or appear in the GROUP BY. Here the extra pivot columns create a "grouping error", see for instance https://stackoverflow.com/questions/16012818/postgresql-grouping-error — challet, Jan 24 '18 at 10:02
That's amazing, @Amine_Dev! I was looking for this answer... So easy! — Gi Tavares, Jun 05 '19 at 17:17
Another use case are `unions` where you can get `SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns` — sabat, Nov 08 '20 at 01:04

score 3 · Answer 3 · answered Nov 30 '19 at 05:34

If you want to remove pivot data then you can use as protected $hidden = ['pivot']; @Amine_Dev suggested, so i have used it but it was not working for me,

but the problem really was that i was using it in wrong model so i want to give more detail in it that where to use it, so you guys don't struggle with the problem which i have struggled.

So if you are fetching the data as :

Item::with('tags')->get();

then you have to assign pivot to hidden array like below

But keep in mind that you have to define it in Tag model not in Item model

class Tag extends Model {

   protected $hidden = ['pivot'];
}

score 2 · Answer 4 · answered Jul 07 '21 at 12:05

Two possible ways to do this

1. using makeHidden method on resulting model

$items = Item::with('tags')->get();
return $items->makeHidden(['pivot_col1', 'pivot_col2']...)

2. using array_column function of PHP

$items = Item::with('tags')->get()->toArray();

 return array_column($items, 'tags');

Laravel Removing Pivot data in many to many relationship

4 Answers4