如何使用Laravel Eloquent基于子女关系进行选择

Question

我为商家提供了以下表格结构；动态滤波器

Merchant Table

id | name 
-------------------------
1 | Food Supplier  

Filters

id | name 
-------------------------
1 | Service Area
2 | Service Cost

Filter Values

id | filter_id | value
-------------------------
1 | 1         | Africa
2 | 1         | Europe
3 | 2         | 2000-4000
4 | 2         | 4000-6000

Merchant Filter Values

id | filter_id | value_id | merchant_id
----------------------------------------
1 | 1         | 1        | 1
2 | 1         | 2        | 1
3 | 2         | 4        | 1

我运行以下查询来使用过滤器搜索商家：

select * 
from `merchants`
where `name` LIKE "%search_term%"
and exists (
select * 
from `filter_values` 
inner join `merchant_filter_values`
on `filter_values`.`id` = `merchant_filter_values`.`value_id` 
where `merchants`.`id` = `merchant_filter_values`.`merchant_id`
and (`filter_values`.`id` = 1 and filter_values.filter_id = 2)
or (`filter_values`.`id` = 2 and filter_values.filter_id = 3)
or etc.
)

查询运行良好，并按预期返回结果，唯一的问题是它不能很好地解释，因此它不会返回模型中的附加变量/函数，例如featured_img_url在结果中没有返回：

protected $appends = array('featured_img_url');
public function getFeaturedImgUrlAttribute()
{
$img_path = Config::get('app.asset_url') . "/merchants/featured/" . $this->id . ".";
return $img_path . $this->featured_image_ext . $this->getLastUpdatedUrl();
}

问题是，我如何用拉拉威尔雄辩的语言写出这样的疑问？

Answer 1

您必须使用hydrate()函数将select查询的结果转换为雄辩集合。你可以使用这样的东西：

$merchants = DB::select( "select * 
from `merchants`
where `name` LIKE '%search_term%'
and exists (
select * 
from `filter_values` 
inner join `merchant_filter_values`
on `filter_values`.`id` = `merchant_filter_values`.`value_id` 
where `merchants`.`id` = `merchant_filter_values`.`merchant_id`
and (`filter_values`.`id` = 1 and filter_values.filter_id = 2)
or (`filter_values`.`id` = 2 and filter_values.filter_id = 3)
or etc.
)" );
return Merchant::hydrate($merchants);

您还必须定义商户模型。