doutanggun9816 2019-04-22 11:09
浏览 88
已采纳

在laravel 5.8中优化请求

I have been stuck since yesterday on a request that I can not solve. I make a site to manage an abandoned dog shelter. Dogs must be vaccinated:

  • A first time in their life then a month later for a reminder
  • Then once a year

So I am looking for from my table "animals" and my table "soinsveto" to list all dogs that have not had vaccines for over a year or if it is their first vaccine since more a month.

The goal is to put an alert in the administration interface that will say "attention this dog is not vaccinated, it should have been before the ...)

my table "animals"

id | name |

My table "soinveto" (typesoin_id = 1 if it is the first vaccine and there is a delay of one month before the next vaccine and 2 if the vaccine has a period of one year

id | typesoin_id | animal_id | datedusoin

Animal model

public function soinsvetos() {
    return $this->hasMany(Soinsveto::class, 'animal_id');
}

soinveto model

public function animal()
{
    return $this->belongsTo(Animal::class, 'animal_id')->withTrashed();
}

I am looking to select:

  • all the animals ("table animals") with the single last vaccination ("soinsvetos" table with typesoin_id = 1 or 2) by date ("datedusoin" field). I prefer to do with the dates because it is possible that the users seize vaccines in the disorder. Work on the "id" risk of Do not walk.

  • Then if "typesoin_id" is 1 I check that there is not more than one month with the date of today and if it is 2 that there is not more than one year.

I found a solution that works but is not optimized at all as you can see :

public function compose(View $view)
{

    foreach (\App\Animal::get()
                 ->all() as $animal) {

        $soins = \App\Soinsveto::with('typesoin')
            ->where('animal_id', $animal->id)
            ->where(function ($q) { /// 1 = primovaccin et 2 = vaccins annuels
                $q->where('typesoin_id', '=', '2')
                    ->orWhere('typesoin_id', '=', '1');
            })
            ->orderBy('datedusoin', 'desc')
            ->get()
            ->first();
        if ($soins) {

            if ($soins->typesoin_id == '1' && $soins->datedusoin < Carbon::now()->subMonths(1)) {
                $crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();

            }
            if ($soins->typesoin_id == '2' && $soins->datedusoin < Carbon::now()->subYears(1)) {
                $crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();

            }

        }
        $notif[] = [
            'title' => $soins->typesoin->nomsoin,
            'start' => $crudFieldValue,
            'nomanimal' => $animal->nom,
        ];
    }
    $view->with('notif', $notif);


}

With my version all the animals are loaded which makes a huge number of sql requests. I can not find any solutions to make this happen. I tried a lot of things but I can not get everything together in a single optimized query

  • 写回答

1条回答 默认 最新

  • duanguan1573 2019-04-22 15:52
    关注

    Following up on my comments to the question, I suggest you rewrite your foreach statement to the code below. It uses the with function to eager load the soinsvetos relationship in all animals so that you do not end up with the N+1 problem.

    foreach (\App\Animal::with('soinsvetos')->get() as $animal) {
    
        $soins = $animal->soinsvetos
                        ->filter(function ($soinsveto) {
                            /// 1 = primovaccin et 2 = vaccins annuels
                            return $soinsveto->typesoin_id == 1 || $soinsveto->typesoin_id == 2;
                        })
                        ->sortBy('datedusoin', 'desc')
                        ->first();
    
        if ($soins) {
            if ($soins->typesoin_id == '1' && $soins->datedusoin < Carbon::now()->subMonths(1)) {
                $crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();
            }
            if ($soins->typesoin_id == '2' && $soins->datedusoin < Carbon::now()->subYears(1)) {
                $crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();
            }
        }
    
        $notif[] = [
            'title' => $soins->typesoin->nomsoin,
            'start' => $crudFieldValue,
            'nomanimal' => $animal->nom,
        ];
    
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型