[FIXED] How to search into relation table?

Issue

I have two tables, first is Category table that has id and name columns.

Second table is Product that has id, category_id and name columns.

public function category() {
    return $this->belongsTo(Category::class);
}

My question is how to search product's name, and it's category name in one Eloquent or DB query?

---

I tried the below code, it work for products name but when I search the category name it returns nothing.

$searchText = $request->input('search');
Product::query()
    ->where(function($query) use($searchText){
        if($searchText) {
            $query->where('name', 'LIKE', '%'.$searchText.'%')
                ->orWhere(function($query2) use($searchText) {
                    $query2->with(['category' => function($query3) use ($searchText){
                        $query3->where('name', 'like', '%'.$searchText.'%');
                    }]);
                });
        }
    })
    ->paginate(8);

And also, I would like to ask which is best to use this scenario Eloquent or DB?

Solution

$query = Product::query()->with('category')
            ->where(function($query) use($request) {
        if ($request->input('search')) {
            $search_text = $request->input('search');
            $query->where('name', 'Like', '%' . $search_text . '%')
            ->orWhereHas('category', function ($query2)use($search_text) {
                $query2->where('name', 'Like', '%' . $search_text . '%');
            });
        }
    });

    $query1 = $query->paginate(8);

Hope fully it will help to you easily.

Answered By - Ankita Dobariya

Answer Checked By - Clifford M. (PHPFixing Volunteer)