php – Laravel – 插入前检查数据库参数的有效方法

public function store(Request $request,$id,$inid)
{       
    $startOfDay = Carbon::Now()->startOfDay();
    $endOfDay = Carbon::Now()->endOfDay();

    $instruments = InstrumentReading::whereBetween('created_at',[$startOfDay,$endOfDay])
                                    ->where('iv_inid','=',$inid)
                                    ->get();
    foreach ($request->input() as $k => $v) {
        $read = new InstrumentReading;
        $read->iv_inid = $inid;
        $read->iv_ipid = $k;
        $read->iv_usid = Auth::user()->id;
        $read->iv_reading = $v;
        $read->save();
    }
    if ($instruments->count() > 0) {            
        //to filter the iv_ipid...
        foreach($instruments as $instrument)
        {
            $instrument->iv_status = "VOID";
            $instrument->save();
        }
    }

}

用效率方法来说,您可以做的是简单地检查/获取数据库中的所有可行行,如果行已经插入,则检查循环.也只获取iv_ipid列,因为我们不需要表中的所有列进行检查.选择我们需要的列会更快.您可以直接使用Fluent( Query Builder)来强调从数据库中提取数据,因为它可以大大提高简单查询的性能.

public function store(Request $request,$inid)
{
    // Search only records with submitted iv_ipid,iv_inid and created today
    $alreadyInserted = DB::table('instrument_readings')
                   ->whereBetween('created_at',[
                       Carbon::Now()->startOfDay(),Carbon::Now()->endOfDay()
                   ])
                   // Get only records with submitted iv_ipid
                   ->whereIn('iv_ipid',array_keys($request->input()))
                   // Get records with given iv_inid only
                   ->where('iv_inid',$inid)
                   // For our check we need only one column,// no need to select all of them,it will be fast
                   ->select('iv_ipid')
                   // Get the records from DB
                   ->lists('iv_ipid');

    foreach ($request->input() as $k => $v) {
        // Very simple check if iv_ipid is not in the array
        // it does not exists in the database
        if (!in_array($k,$alreadyInserted)) {
            $read = new InstrumentReading;
            $read->iv_inid = $inid;
            $read->iv_ipid = $k;
            $read->iv_usid = Auth::user()->id;
            $read->iv_reading = $v;
            $read->save();
        } else {
            //todo
        }
}

这是迄今为止最有效的方法,因为您只需立即获取您感兴趣的记录,而不是从今天开始的所有记录.另外,您只能获取一列,我们需要检查一列.雄辩地表现出对性能的大量过热,所以在建议的代码中,我直接使用Fluent,这将提高这部分代码的执行速度〜20％.

你在原代码中的错误是每次循环执行数据库调用.当您需要像检查这样简单的任务时,不要将数据库调用,查询等放在循环中.这是一个过度的杀戮.而是在循环之前选择所有需要的数据,然后进行检查.

现在这是为了防止您只需要将新的记录保存到数据库.如果你想操纵循环中的每个记录,我们假设你需要循环遍历每个被提交的条目,获取模型或创建它,如果它不存在,然后用这个模型做其他事情,那么最有效的方式是这样的：

public function store(Request $request,$inid)
{
    foreach ($request->input() as $k => $v) {
       // Here you search for match with given attributes
       // If object in DB with this attributes exists
       // It will be returned,otherwise new one will be constructed
       // But yet not saved in DB
       $model = InstrumentReading::firstOrNew([
           'iv_inid' => $inid,'iv_ipid' => $k,'iv_usid' => Auth::user()->id
       ]);

       // Check if it is existing DB row or a new instance
       if (!$model->exists()) {
           // If it is a new one set $v and save
           $model->iv_reading = $v;
           $model->save();
       }

    // Do something with the model here
    .....   
}

这样Laravel会检查数据库中是否存在已传递参数的模型,如果是,则返回给您.如果它不存在,它将创建它的新实例,所以你可以设置$v并保存到db.所以你很乐意用这个模型去做任何事,你可以确定它存在于数据库之后.