我正在尝试从输入字段创建一个搜索函数，以搜索多个关联在一起的表

Question

我正在尝试实现对多个表的搜索。该项目是一个网上商店，有3张与众不同的桌子，产品，类别和品牌。我只能在"产品"表中搜索，但似乎无法从刀片文件中获得相同的搜索字段来搜索类别或品牌并返回相关产品的结果。我的刀片搜索输入

<form action="{{ url('/search-products') }}" method="post">
{{ csrf_field() }}
<div class="form-group">
<div class="row" style="display: flex;">
<input class="typeahead form-control form-control-lg" type="text" name="product" id="product" placeholder="Search item to buy here..." style="width: 220px;"> &nbsp
<button type="submit" class="btn btn-primary">
<i class="ti-search"></i>
</div>
</div>
</form>

我的控制器中的搜索功能

<?php
namespace AppHttpControllers;
use IlluminateHttpRequest;
use IlluminateSupportFacadesInput;
use IlluminateSupportFacadesRedirect;
use Image;
use AppCategory;
use AppBrands;
use AppProduct;
use DB;
class ProductController extends Controller
{
public function searchProducts(Request $request) {
$product = $request->input('product');
$categories = Category::with('categories')->where(['parent_id' => 0])->get();
$productsAll = Product::query()->where('product_name', 'LIKE', "%{$product}%")
->orWhere('state', 'LIKE', "%{$product}%")
->orWhere('lga', 'LIKE', "%{$product}%")
->orWhere('category_id', 'LIKE', "%{$product}%")->where('status', 1)->get();
$breadcrumb = "<a href='/'>Home</a> / ".$product;
return view('pages.results')->with(compact('categories', 'productsAll',  'product', 'breadcrumb'));
}
}

我的产品型号

<?php
namespace App;
use IlluminateDatabaseEloquentModel;
use Auth;
use Session;
use DB;
class Product extends Model
{
protected $with = ['category'];
public function category()
{
return $this->hasMany('AppCategory', 'id');
}
}

我的分类模型

<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Category extends Model
{
public function products(){
return $this->hasMany('AppProduct','id');
}
}

我的品牌模型

<?php
namespace App;
use IlluminateDatabaseEloquentModel;
class Brands extends Model
{
//
}

Answer 1

要查找与给定searchTerm一样具有Products的Categories，可以执行以下操作：

SearchController.php

class SearchController extends Controller
{
public function index(Request $Request)
{
$searchTerm = $request->product;
$categories = Categories::whereHas('products', function ($query) use ($searchTerm){
$query->where('name', 'like', '%'.$searchTerm.'%');
})
->with(['products' => function($query) use ($searchTerm){
$query->where('name', 'like', '%'.$searchTerm.'%');
}])->get();
return view('search-results-view', compact('categories));
}
}

然后，您将有一个Categories的collection，其中Products已加载。

将$categories传递到返回的视图(如上(后，您将能够在刀片文件中的Categories和相关的Products上循环。

搜索结果视图.blade.php

@foreach ($categories as $category)
<h4>{{ $category->name }}</h4>
@foreach ($category->products as $product)
{{ $product->name }}
@endforeach
@endforeach

您可以使用相同的主体来处理您的Brands模型。

Answer 2

我能够通过使用雄辩器的联接函数来查询多个表来解决这个问题。我在我的控制器中编辑了我的搜索功能，如下

public function searchProducts(Request $request) {
$product = $request->input('product');
$categories = Category::with('categories')->where(['parent_id' => 0])->get();
$productsAll = Category::query()->join('products', 'products.category_id', '=', 'categories.id')
->where('categories.name', 'LIKE', "%{$product}%")
->orWhere('products.product_name', 'LIKE', "%{$product}%")
->where('products.status', 1)->get();

$breadcrumb = "<a href='/'>Home</a> / ".$product;
return view('pages.results')->with(compact('categories','productsAll','product','breadcrumb'));
}

和我的分类控制器

class Category extends Model implements Searchable
{
protected $table = 'categories';
protected $fillable = [
'name'
];
public function categories(){
return $this->hasMany('AppCategory','id');
}
public function products(){
return $this->hasMany('AppProduct','id');
}
}

和我的产品控制器

class Category extends Model implements Searchable
{
protected $table = 'categories';
protected $fillable = [
'name'
];
public function categories(){
return $this->hasMany('AppCategory','id');
}
public function products(){
return $this->hasMany('AppProduct','id');
}
}