我的表具有以下结构,一个产品可以具有许多skus:
product skus
id product_id
sku_prin sku
other fields other fields
如果在产品表的SKU_PRIN中存在搜索值或SKU表的SKU,则应选择行。
$search_value = "ramdom_value";
$query = product::query();
$result = $query->with(['skus' => function($q) use($search_value){
// this code won't work the orWhere cannot change the context of skus table
$q->where('sku', $search_value)->orWhere('products.sku_prin', $search_value)
}])->paginate(50);
上面是我失败的尝试。如何完成我想要的?
一种方法是使用雄辩的功能
认为您具有这样的产品和SKUS模型
class Product extends Model
{
protected $table = 'product';
public function sku()
{
return $this->belongsTo('AppSkus', 'product_id', 'id');
}
}
class Skus extends Model
{
protected $table = 'skus';
public function products()
{
return $this->hasMany('AppProduct', 'id', 'product_id');
}
}
您可以像这样的雄辩获得数据
$keyword = 'some keyword'
Product::where('sku_prin', '=', $keyword) // for product table
->orWhereHas('sku', function($query) use ($keyword) {
$query->where('sku', '=', $keyword); // for sku table
});
甚至更多
$keywordString = 'keyword1 keyword2';
$keywords = explode(' ', $keywordString);
Product::where(function($query) use ($keywords) { // for product table
foreach ($keywords as $keyword)
{
$query->orWhere('sku_prin', 'like', "%$keyword%");
}
})
->orWhereHas('sku', function($query) use ($keywords) { // for sku table
foreach ($keywords as $keyword)
{
$query->orWhere('sku', '=', $keyword);
}
});