嵌套3深时，在laravel查询上急于加载 [英] Eager Loading on laravel query when nested 3 deep

问题描述

我的Laravel网站上有Jobs and Projects，它们之间有关系.我正在尝试减少查询的数量(并注意N + 1问题).我这样做是为了在输出JobItems时渴望在控制器中加载.

I have Jobs and Projects in my Laravel site and there is a relationship between them. I'm trying to reduce the number of queries made (and be mindful of N+1 problem). I do this with eager loading in my controller when outputting my JobItems.

在嵌套时如何执行此操作:JobItems> Project> JobItems.

How can I do this when I nest: JobItems > Project > JobItems.

在一种情况下会发生这种情况，而我却得到了重复的查询.我不确定如何渴望加载.我将概述这种情况:

There is one instance where this happens and I am getting duplicate queries. I'm not sure how to eager load. I'll outline the situation:

每个JobItem都有一个小时列.

Each JobItem has a column for hours.

在我的项目中，我对所有关联的JobItem小时进行求和，以确定项目中的总时数. (例如，每个有4个小时的3个JobItem，然后在我的项目模型中有一个访问器，显示为projectHours = 12).

In my project I SUM all associated JobItem hours to determine the total hours in a project. (Eg. 3 JobItems with 4hours each, I then have a an accessor in my Project Model that says projectHours = 12).

• 工作
  • 项目
    • project_hours(相关作业的总和)
    • job
      • project
        • project_hours (SUM of associated jobs)

        我想列出所有JobItems和每个JobItem以具有相关Project的子元素.

        I want to list all JobItems and each JobItem to have a child element of the related Project.

        所以我打电话给

        1. JobItem模型
        2. 项目模型(作为孩子放入JobItem中)
        3. JobItem模型(用于计算项目模型中的总工时)

        在第3步中，我收到N + 1个问题和多个重复的查询.我想通过急切的加载减少这种情况，但不确定如何(在步骤1中)已经调用JobItem模型.

        At this step 3 I am getting N+1 issue and multiple duplicate queries. I'd like to reduce this with eager loading, but not sure how if I am (in step 1) already calling the JobItem model.

        在我的控制器中，我有:

        In my controller I have:

            public function getJobItems()
            {
        
                $userId = auth()->user()->id;
        
                return JobItem::whereHas('project', function ($query) use ($userId) {
                    $query->where('user_id', '=', $userId);
                })->with(['project', 'user'])
                    ->get();
            }
        
        

        在我的Project模型中，我有:

        In my Project model I have:

        class Project extends Model
        {
        
            protected $appends = ['projectHours'];
        
        
            public function jobs()
            {
                return $this->hasMany('App\JobItem', 'project_id', 'id');
            }
        
            public function getProjectHoursAttribute()
            {
                return $this->jobs->sum('hours');
            }
        
        }
        
        

        和往常一样，如果我以错误的方式处理此问题，请告知我.非常感谢.

        As always, if I am approaching this incorrectly let me know. Much appreciated.

        NB.这与有关通过急切加载减少查询的SO凭单有关:

        NB. This is related to this SO ticket about reducing queries with Eager Loading: How to use eager loading on Laravel model with SUM of a relationship - currently getting multiple queries (N+1)

        推荐答案

        我将分两部分进行回答:

        I'm going to answer this in two parts:

        1)预加载关系

        因此，如果您尝试列出一个jobItem集合，但想要显示每个与项目相关的Project的Job的属性，则可以像这样预先加载所需的关系:

        So if you are try to list out a jobItem collection but want to display an attribute of each item's associated Project's Jobs, you can preload the required relationships like so:

        JobItem::with(['project.jobs'])->get();
        

        .运算符可用于访问嵌套关系. (它是与加载任何关系相同的规则，因为它是各个模型上的关系的名称)

        The . operator can be used to access nested relationships. (It follows the same rules as loading any relationship in that it's the name of the relationship on the respective model)

        2)访问关系

        我注意到，即使您有可用的User模型，您也要通过用户ID约束查询.如果您在User模型上具有jobItems关系，则可以改为执行以下操作:

        I notice that you are constraining a query by a user id even though you have a User model available. Provided you have a jobItems relationship on you User model, you could do this instead:

        public function getJobItems()
        {
            $user = auth()->user()->loadMissing(['jobItems.project.jobs']);
        
            return $user->jobItems;
        }
        

        这篇关于嵌套3深时，在laravel查询上急于加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！