我有一个查询只是为了检索数据,当我试图从下面的查询中提取10k数据时,它将花费1.7分钟以上。这是合理的,或者我这次该如何改进。
Vehicles::leftJoin('vehicle_details', 'vehicle_details.id', 'vehicles.vehi_details_id')
->leftJoin('vehicle_extras', 'vehicle_extras.vehicle_id','vehicles.id')
->leftJoin('reasons', 'reasons.vehicle_id', 'vehicles.id')
->leftJoin('revised_vehicles', 'revised_vehicles.vehicle_id','vehicles.id')
->leftJoin('makes', 'makes.model_id', 'vehicle_details.model_id')
->leftJoin('bidder_invoices', 'bidder_invoices.vehicle_id', '=', 'vehicles.id')
->leftJoin('bidding_views', 'bidding_views.vehicle_id', '=', 'vehicles.id')
->leftJoin('bidder_prs', 'bidder_prs.vehicle_id', '=', 'vehicles.id')
->leftJoin('bidder_remarks', 'bidder_remarks.vehicle_id', '=', 'vehicles.id')
->leftJoin('vehicle_pins', 'vehicle_pins.vehicle_id', '=', 'vehicles.id')
->leftJoin('translations', 'translations.vehicle_id', '=', 'vehicles.id')
->where(function ($query) {
$query->where('vehicles.approve', '=', 1)
->orWhereNull('vehicles.approve');
})
->orderBy('vehicles.site', 'ASC')
->orderBy('vehicles.auc_time', 'ASC')
->select([
'vehicles.*',
'vehicle_details.color as vehi_color',
'vehicle_details.grade as vehi_grade',
'vehicle_details.images',
'makes.model_name',
'vehicle_extras.id as extra_details_id',
'vehicle_extras.ic_remarks',
'vehicle_extras.bidder_adj',
'reasons.fob_issue',
'reasons.fob_approve',
'reasons.decline_remark',
'reasons.tc_issue',
'reasons.fob_overruling_remark',
'bidder_invoices.invoice_name',
'bidding_views.reason as bidder_reason',
'bidder_prs.bidder_mb as bidder_mb',
'bidder_remarks.bidder_remarks as bidder_remarks_daily',
'revised_vehicles.old_mb_round',
'translations.assigned_translator'
])
->paginate(10000);
我还为每个联接列添加了索引。此外,我还使用MySQL作为我的数据库
您可以使用Eloquent关系,因为Eloquet模型类中的方法引用EloquentRelationship如果关系为1:1,请使用hasOne如果关系是1:M,则use有Many如果关系是M:1,则使用属于
在您的车辆型号中
例如
对于1:1
public function vehicleDetails() {
return $this->hasOne(VehicleDetail::class,'vehi_details_id','id');
}
对于1:M
public function VehicleExtras() {
return $this->hasMany(VehicleExtras::class,'id','vehicle_id');
}
对于M:1
public function VehicleMakes() {
return $this->belongsTo(VehicleMakes::class,'model_id','model_id');
}
在建立了所有的关系后,你可以得到带有过滤器的车辆表,如下
$data['vehicles'] = Vehicles::whereNull('vehicles.deleted_at')
->where(function ($query) {
$query->where('vehicles.multiple_po', 0)
->orWhereNull('vehicles.multiple_po');
})
->where(function ($query) {
$query->where('vehicles.approve', '=', 1)
->orWhereNull('vehicles.approve');
})
->orderBy('vehicles.site', 'ASC')
->orderBy('vehicles.auc_time', 'ASC')
->paginate(10000);
然后,
你可以将刀片中的车辆正常放置,如果你需要调用关系表,则它属于某个id,
要从车辆详细信息中获得描述,您可以访问以下
$data['vehicles']->vehicleDetails->description
或者从品牌表中获取车辆的品牌
$data['vehicles']->VehicleMakes->name