laravel 7.14 build link for product after searching datatable with relation

I have a laravel 7.14 app.

I’m trying to get the search result based on brand name = query.

The problem is with building link to display individual product. It takes brand slug instead of product slug. When I search with brand name I get the desired result but to view a individual product link fetches the brand slug instead of product slug. If I remove the slug from brands table I get an error in displaying results.

My products table has brand_id which tags the brand in product. In brands table I have ‘name’ and ‘slug’.

Products fields:

'image',
'title',
'slug',
'description',
'brand_id',
'category_id',

Brands fields:

'name',
'slug',

Here’s my search in ProductsController:

public function search(Request $request)
{
    $validator = Validator::make($request->all(), [
    'query' => 'required|min:3',
    ]);

    if ($validator->fails()) {
    return back()->with('toast_error', 'Please enter at least 6 character');
    }

    $query = $request->input('query');

    $products = Product::where('title', 'like', "%$query%")
                        ->orWhere('molecules', 'like', "%$query%")
                        ->orWhere('text', 'like', "%$query%")
                        ->join('brands', 'brands.id', '=', 'brand_id')
                        ->orWhere('name', 'like', "%$query%")->paginate(10);

    // Search with paginate
    // $products = Product::search("%$query%")
    //             ->paginate(20);

    $categories = Category::all();
    $brands = Brand::all();

    return view('products.search')->with([
        'products' => $products,
        'categories'=> $categories,
        'brands' => $brands,
    ]);
}

Answer

Just give alias to one of the slug columns in your query. For example

$products = Product::select('products.*', 'brands.name', 'brands.slug as brand_slug')
                        ->where('title', 'like', "%$query%")
                        ->orWhere('molecules', 'like', "%$query%")
                        ->orWhere('text', 'like', "%$query%")
                        ->join('brands', 'brands.id', '=', 'brand_id')
                        ->orWhere('name', 'like', "%$query%")->paginate(10);