java+join替代_laravel 5通过替换Join来优化whereHas

我有一个带过滤器的车辆列表页面 . 我创建了一个范围，用于将选定的过滤器应用于车辆表 . 范围利用了雄辩的whereHas方法 . 但是在执行查询时它会出现严重的性能问题，因为我看到每个whereHas都转换为子查询而不是Join . 请参阅下面的我的范围函数和查询运行

public function scopeApplyFilter($query, $conditions, $featured=0)

{

$query->where(function($q) use ($conditions){

if ($conditions->get('content')) {

$q->where('text', 'like', '%'.$conditions->get('content').'%' );

}

if ($conditions->get('condition')) {

$q->where('condition', $conditions->get('condition'));

}

if ($conditions->get('transmission')) {

$q->where('transmission', $conditions->get('transmission'));

}

if ($conditions->get('price')) {

$range = explode('-', $conditions->get('price'));

$q->where('price', '>=',$range[0]);

$q->where('price', '<=',$range[1]);

}

if ($conditions->get('odometer')) {

$range = explode('-', $conditions->get('odometer'));

$q->where('odometer', '>=',$range[0]);

$q->where('odometer', '<=',$range[1]);

}

if ($conditions->get('year')) {

$range = explode('-', $conditions->get('year'));

$q->where('year', '>=',$range[0]);

$q->where('year', '<=',$range[1]);

}

return $q;

})->whereHas('model', function($q) use ($conditions) {

if ($conditions->get('model')) {

$q->where('model_name', $conditions->get('model'));

}

return $q;

})->whereHas('make', function($q) use ($conditions) {

if ($conditions->get('make')) {

$q->where('make_name', $conditions->get('make'));

}

return $q;

})->whereHas('dealer.province', function($q) use ($conditions) {

if ($conditions->get('province')) {

$q->where('province_name', $conditions->get('province'));

}

return $q;

})->whereHas('dealer', function($q) use ($conditions, $featured) {

if($featured){

$q->where('featured', 1);

}

if ($conditions->get('lat')) {

//$conditions->put('distance',5000);

$lat = $conditions->get('lat');

$lon = $conditions->get('lon');

$lat = 53.421879;

$lon = -113.4675614;

$q->select(DB::raw("id, ( 6371 * acos( cos( radians($lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( latitude ) ) ) ) AS distance"))->having('distance','get('distance')); //3959 for miles

}

return $q;

});

}

查询运行是

SELECT *

FROM `vehicles`

WHERE EXISTS (SELECT *

FROM `models`

WHERE `vehicles`.`model_id` = `models`.`id`)

AND EXISTS (SELECT *

FROM `makes`

WHERE `vehicles`.`make_id` = `makes`.`id`)

AND EXISTS (SELECT *

FROM `dealers`

WHERE `vehicles`.`dealer_id` = `dealers`.`id`

AND EXISTS (SELECT *

FROM `provinces`

WHERE `dealers`.`province_id` =

`provinces`.`id`))

AND EXISTS (SELECT id,

( 6371 * Acos(Cos(Radians(53.421879)) *

Cos(Radians(latitude)) *

Cos(

Radians(longitude)

- Radians(

-113.4675614))

+

Sin(Radians(53.421879)) *

Sin(Radians(latitude)))

) AS

distance

FROM `dealers`

WHERE `vehicles`.`dealer_id` = `dealers`.`id`

HAVING `distance` < 200)

AND `status_id` = 1

ORDER BY `created_at` DESC

LIMIT 15 offset 0

501.16毫秒

我正在使用Laravel 5.2 . 制造，模型和经销商是'属于'车辆的关系 . 一切正常 . 但是由于性能问题，我不得不使用连接重写函数 . 伙计们，请帮助我们根据上面给出的条件使用连接重写此函数的最佳实践 . 非常欢迎您的意见 .