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>

Check the documentation: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence; `whereHas('attributes', function ($query) use ($search){ ... });`, replace `...` with the query for `SKU` against your `$search` variable — Tim Lewis, Sep 15 '21 at 14:07

Atika · Accepted Answer · 2021-09-15T14:43:59.443

2

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'));
}

edited Sep 15 '21 at 14:43

answered Sep 15 '21 at 14:22

Atika

1,025
2
6
17

The problem is: there's only one field for input and that is input('search). I've edited my main thread to show the code of the view search. – user2519032 Sep 15 '21 at 14:27
The idea is: user can search for product by name and by sku in a single field. BTW, I didn't downvoted your answer. – user2519032 Sep 15 '21 at 14:31
@Atika It's a free-form text field; the user can enter a name like `'Example'` or a SKU like `'ABC1234'`; you don't need multiple form-fields to handle this, you simply need to code it to check against either the `name` column or the `sku` column (which is what this question is asking for help on doing). – Tim Lewis Sep 15 '21 at 14:36
1

Every product has SKU, which is available in the product page. That SKU is a part of attributes because sometimes there's a product with different sizes and the SKU is different. So, basically the issue here is: how can I search from both tables? – user2519032 Sep 15 '21 at 14:36
Sidenote, this answer has the right idea, but the addition of the `$sku` variable is redundant; if you use `$search` for both methods, and maybe adjust one of them to `orWhereHas()` or `orWhereTranslationLike()` (if that is available), then it should work. – Tim Lewis Sep 15 '21 at 14:39
@Tim I already tried with adding orWhereTranslationLike() but I'm getting this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product_translations.sku' in 'where clause' (SQL: select * from `products` where exists... – user2519032 Sep 15 '21 at 14:43
That's because sku is in products_attributes table, not products_translations – user2519032 Sep 15 '21 at 14:43
@Atika products_attributes – user2519032 Sep 15 '21 at 14:45
I've just tried your updated answer but I'm getting this error: Argument 1 passed to App\Http\Controllers\PageController::App\Http\Controllers\{closure}() must be an instance of App\Http\Controllers\Builder... – user2519032 Sep 15 '21 at 14:46
Remove the `Builder` keyword from `function (Builder $q)`, or import it as a `use Illuminate\Database\Eloquent\Builder;` at the top of the Controller. Namespacing issue there. – Tim Lewis Sep 15 '21 at 14:49
2

Yees, that's it! It's working great! Thank you very much! – user2519032 Sep 15 '21 at 14:51

Laravel search from translation table and relationship

1 Answers1