Nested relation whereHas in laravel

Question

I wanna ask about some nested relation using whereHas query in laravel, well for the first I will explain the model first before I going through into my main case.

this is my model :

StockIn.php

class StockIn extends Model {
    protected $primaryKey = "id_stock_in";

    /* this only column that I wanna show, and skip the else column */
    protected $fillable = ['stock_in_id_type'];

    public function type_of_items() {
        return $this->belongsTo('App\TypeOfitem', 'stock_in_id_type');
    }
}

TypeOfItem.php

class TypeOfItem extends Model {
    protected $primaryKey = "id_type_item";

    /* this only column that I wanna show, and skip the else column */
    protected $fillable = ['type_id_item'];

    public function items() {
       return $this->belongsTo('App\Item', 'type_id_item');
    }

    public function stock_ins() {
       return $this->hasMany('App\StockIn');
    }
}

Item.php

class Item extends Model {
    protected $primaryKey = "id_item";

    /* this only column that I wanna show, and skip the else column */
    protected $fillable = ['item_id_common_unit'];

    public function common_units() {
       return $this->belongsTo('App\CommonUnit', 'item_id_common_unit');
    }

    public function type_of_items() {
       return $this->hasMany('App\TypeOfItem');
    }
}

CommonUnit.php

class CommonUnit extends Model {
    protected $primaryKey = "id_common_unit";

    /* this only column that I wanna show, and skip the else column */
    protected $fillable = [/* describe column */];

    public function items() {
       return $this->hasMany('App\Item');
    }
}

I already describe all of my model, as you can see all table (child) have some relation to each parent like :

stockIn -> typeOfItem (relation between child and parent)

typeOfItem -> Item (relation between child and parent)

Item -> CommonUnit (relation between child and parent)

so for the question is how to make some query to getting data in nesting relationship when I do search for all data in child or parent? I already made the query but the result is not same with my expectation or null, it can be said that.

StockInController

$getData = StockIn::with(['type_of_items' => function ($query) {
            $query->select('id_type_item', 'type_id_item', 'code_type_of_item', 'type_of_item')
            ->with(['items' => function ($query) {
                $query->select('id_item', 'item_id_common_unit', 'name_item')
                ->with(['common_units' => function ($query) {
                    $query->select('id_common_unit', 'name_unit');
                }]);
            }]);
        }])
        ->with(['stock_out_left_join' => function ($query) {
            $query->select('id_stock_out', 'stock_out_id_stock_in');
        }])
        ->whereHas('type_of_items', function ($query) use ($search) {
            $query->where('code_type_of_item', 'like', "%{$search}%");
        })
        ->whereHas('type_of_items.items', function ($query) use ($search) {
            $query->orWhere('name_item', 'like', "%{$search}%");
        })
        ->whereHas('type_of_items.items.common_units', function ($query) use ($search) {
            $query->orWhere('name_unit', 'like', "%{$search}%");
        })
        ->orWhere('created_by', 'like', "%{$search}%")
        ->orWhere('edited_by', 'like', "%{$search}%")
        ->get()
        ->toArray();

Oh ya I will send example data for my query in this bellow :

but when I do search with some keyword is not worked, for example when I do type "adaptor", the result is empty or nothing show on my data, so what I must to do? Thank you

Answer 1

Okay for a while I was think about my problem finally I got the answer. Okay for sharing to everyone I will explain a little bit for the answer.

So the query what I wrote on the controller, I change into this :

$getData = StockIn::with(['type_of_items' => function ($type_of_item) {
            $type_of_item->select('id_type_item', 'type_id_item', 'code_type_of_item', 'type_of_item')
            ->with(['items' => function ($item) {
                $item->select('id_item', 'item_id_common_unit', 'name_item')
                ->with(['common_units' => function ($common_unit) {
                    $common_unit->select('id_common_unit', 'name_unit');
                }]);
            }]);
        }])
        ->with(['stock_out_left_join' => function ($stock_out_left_join) {
            $stock_out_left_join->select('id_stock_out', 'stock_out_id_stock_in');
        }])
        ->whereHas('type_of_items', function ($type_of_items_search) use ($search) {
            $type_of_items_search->where('code_type_of_item', 'like', "%{$search}%")
                ->orWhere('type_of_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items', function ($items_search) use ($search) {
            $items_search->where('name_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items.common_units', function ($common_units_search) use ($search) {
            $common_units_search->where('name_unit', 'like', "%{$search}%");
        })
        ->orWhere('created_by', 'like', "%{$search}%")
        ->orWhere('edited_by', 'like', "%{$search}%")
        ->get()
        ->toArray();

As you can see my new query has a new parameter in every with function and I was naming all the parameter with different name, and not like first name before query, so the problem is the ambiguous paramater in every single with function because this query based on nested relation so I must make the parameter name different each other. Or you want make them into split one by one and not using the nested with function you can use this query too, I put on this bellow :

$getData = StockIn::with(['type_of_items' => function ($query) {
            $query->select('id_type_item', 'type_id_item', 'code_type_of_item', 'type_of_item');
        }])
        ->with(['type_of_items.items' => function ($query) {
            $query->select('id_item', 'item_id_common_unit', 'name_item');
        }])
        ->with(['type_of_items.items.common_units' => function ($query) {
            $query->select('id_common_unit', 'name_unit');
        }])
        ->with(['stock_out_left_join' => function ($query) {
            $query->select('id_stock_out', 'stock_out_id_stock_in');
        }])
        ->whereHas('type_of_items', function ($query) use ($search) {
            $query->where('code_type_of_item', 'like', "%{$search}%")
                ->orWhere('type_of_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items', function ($query) use ($search) {
            $query->where('name_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items.common_units', function ($query) use ($search) {
            $query->where('name_unit', 'like', "%{$search}%");
        })
        ->orWhere('created_by', 'like', "%{$search}%")
        ->orWhere('edited_by', 'like', "%{$search}%")
        ->get()
        ->toArray();

I already tried that query and it's work too (with the same name parameter in every single with function).