Laravel search from translation table and relationship

Question

I have three tables: products, products_translations and products_attributes. I'm also using laravel-translatable.

So far I can search by the name of the product with the following code of my controller:

public function search(Request $request) {
  $search = $request->input('search');
  $products = Product::whereTranslationLike('name', '%' . $search . '%')->with('attributes')->get();

  return view('search', compact('products'));
}

In addition I need to display result from the relationship as well. In my case the SKU is part of the products_attributes, which is relationship of the product model.

This is my code of the Product model:

public function attributes(){
    return $this->hasMany(ProductAttribute::class, 'product_id')->orderBy('id', 'asc');
}

When I dump the product with this code in my view:

{{ dump($product) }}

I can see the relationship but how can I search with SKU field as well?

EDIT: Here's the view part of the search form:

<div class="form-search">
  <form action="{{ route('search') }}" method="post">
    @csrf
    <div class="input-group">
      <input type="text" class="form-control" name="search" placeholder="{{ __('t.searchHere') }}..." required>
      <span class="input-group-btn">
        <button type="submit" class="btn btn-default" aria-label="Submit">
            <i class="fas fa-search"></i>
        </button>
      </span>
    </div>
  </form>
</div>

Answer 1

Try something like :

public function search(Request $request) {

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

     $products = Product::with('attributes')->orWhereHas('attributes', function (Builder $q) use ($search) {
         $q->where('YourFieldNameSKU', $search);
     })->orWhereTranslationLike('name', '%' . $search . '%')
       ->get();

     return view('search', compact('products'));
}