Laravel Eloquent Pivot Tables filtered by pivot column on object collection

Question

I've a problem and searching for a nice solution for it.

I've these db tables:

game_objects
game_object_attributes
game_object_game_object_attribute (pivot)

GameObject.php:

public function gameObjectAttributes()
    {
        return $this->belongsToMany('App\Models\GameObjectAttribute')->withPivot('value')->withTimestamps();
    }

GameObjectAttribute.php:

public function gameObjects()
    {
        return $this->belongsToMany('App\Models\GameObject');
    }

Now I'm trying to get all GameObject of an GameObjectType and filtering the result with pivot column 'value'.

$this->model->where('name', $gameObjectTypesEnum)->first()->gameObjects()->join('game_object_game_object_attribute', 'game_objects.id', '=', 'game_object_game_object_attribute.game_object_id')->where('value', '=', '4')->get());

I've used here an join but is there a way to do it with eloquent's relation?

This is returning me all GameObjects of type 'troops':

$this->model->where('name', $gameObjectTypesEnum)->first()->gameObjects()->get();

the 'gameObjects()' returning me a collection and here I can't call something like

$this->model->where('name', $gameObjectTypesEnum)->first()->gameObjects()->withPivot('value', 3)->get();

OR

$this->model->where('name', $gameObjectTypesEnum)->first()->gameObjects()->gameObjectAttributes->wherePivot('value', 3)->get();

Here I can iterate through collection 'gameObjects()' and checking with foreach if the pivot has value = 3 but there must be a better solution than this. I'm new to laravel..

Additionally

I try to get

Get all GameObjects by an GameObjectType => returning a collection**

GameObjectType::where('name', $gameObjectTypesEnum)->first()->gameObjects()
Then I try to filtering trough pivot to get only GameObjects with given GameObjectType and with pivot value e.g. 3.

->join('game_object_game_object_attribute', 'game_objects.id', '=', 'game_object_game_object_attribute.game_object_id')->where('value', '=', '4')->get());

I'm doing something wrong or this is not possible to do it with Eloquent :-(

Thank you all in advance.

Best regards

rkj · Accepted Answer · 2018-06-14T12:44:54.857

4

You can do like this

 $gameObjects = GameObject::where('name', $gameObjectTypesEnum)->whereHas('gameObjectAttributes', function($query)
    {
        $query->where('value', 3); 
    })->get();

edited Jun 14 '18 at 12:44

answered Jun 14 '18 at 12:12

rkj

8,067
2
27
33

I've tried your solution: GameObjectType::where('name', $gameObjectTypesEnum)->first()->gameObjects()->with(array('gameObjectAttributes' => function($query) { $query->where('value', 3); }))->get()); This returns all GameObjects without considering where value = 3. SQL: "select * from "game_objects" where "game_objects"."game_object_type_id" = ? and "game_objects"."game_object_type_id" is not null" – viko91 Jun 14 '18 at 12:26
that did the trick! Great thanks! But I'dont understand why whereHas is working? – viko91 Jun 14 '18 at 12:58
1

To add an explanation: https://stackoverflow.com/questions/30231862/laravel-eloquent-has-with-wherehas-what-do-they-mean – viko91 Jun 14 '18 at 13:05

score 0 · Answer 2 · answered Jun 14 '18 at 11:56

0

If I didn't misundertand it,

->wherePivot('value',4)

addition to your query is the eloquentish way of that join.

answered Jun 14 '18 at 11:56

mutas

361
2
8

I thought so, too. Sql which is generated is this: "select * from "game_objects" where "game_objects"."game_object_type_id" = ? and "game_objects"."game_object_type_id" is not null and "pivot" = ?" there is no connection to pivot table.. and I don't know why... – viko91 Jun 14 '18 at 12:02
eloquent may or may not use direct matching sql queries to get needed information. it can use additional small queries when the info will be used. (eager load, lazy load topic). but i assume that you already tried with wherePivot method with a sample data and couldn't get the desired result. if that's the case, i have no other solution right now. – mutas Jun 14 '18 at 12:17
Yeah I've already test it with wherePivot and withPivot ... :-( – viko91 Jun 14 '18 at 12:26

Laravel Eloquent Pivot Tables filtered by pivot column on object collection

2 Answers2