有没有一种方法可以从Laravel中渴望加载的表中选择字段? [英] Is there a way to select fields from an eager loaded table in Laravel?

问题描述

所以我在下面定义了几个关系表

So I have a couple of relational tables defined below

父数据库:

public function backorderQuantities(){
        return $this->hasMany(BackorderQuantity::class, 'ITEMNMBR', 'ITEMNMBR')->where('SOPTYPE', 5);
    }

关系数据库:

 public function item(){
        return $this->belongsTo(Item::class, 'ITEMNMBR', 'ITEMNMBR');
    }

我这样称呼这种关系:

Item::has('backorderQuantities')
            ->select('ITEMNMBR',
                Item::raw("SUM(QTYONHND) as qty"),
                Item::raw("SUM(QTYONORD) as ordered"),
                Item::raw("SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) as transit"), 
            )
            ->where('PRIMVNDR', Auth::user()->vendor_id)
            ->groupBy('ITEMNMBR')
            ->orderBy($group['field'], $group['sort'])
        );

这很好用，但是我还需要从backorderQuantities中选择字段，以便可以按所述字段进行排序.换句话说，我想做这样的事情:

This works fine, but I need to select fields from within backorderQuantities as well so that I can sort by said fields. In other words I want to do something like this:

 Item::has('backorderQuantities')
            ->select('ITEMNMBR',
                Item::raw("SUM(QTYONHND) as qty"),
                Item::raw("SUM(QTYONORD) as ordered"),
                Item::raw("SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) as transit"),
                'backorderQuantities->QUANTITY'
            )
            ->where('PRIMVNDR', Auth::user()->vendor_id)
            ->groupBy('ITEMNMBR')
            ->orderBy('backorderQuantities->QUANTITY', $group['sort'])

但是我遇到了这样的错误:

But I hit an error like this:

SQLSTATE [42S22]:[Microsoft] [SQL Server的ODBC驱动程序17] [SQL Server]无效的列名'backorderQuantities'.(SQL:从(选择[ITEMNMBR]，从SUM(QTYONHND)作为数量，从SUM(QTYONORD)开始)，SUM((当LOCNCODE类似于'IT-％'THEN QTYONHND END的情况下为CASE))中选择count(*)作为聚合，存在的[IV00102]中的json_value([backorderQuantities]，'$."QUANTITY"')(从[SOP10200]中选择*，其中[IV00102].[ITEMNMBR] = [SOP10200].[ITEMNMBR]和[SOPTYPE] =5)，[PRIMNNDR] =由[ITEMNMBR]创建的YHI组，作为[aggregate_table])

SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'backorderQuantities'. (SQL: select count(*) as aggregate from (select [ITEMNMBR], SUM(QTYONHND) as qty, SUM(QTYONORD) as ordered, SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) as transit, json_value([backorderQuantities], '$."QUANTITY"') from [IV00102] where exists (select * from [SOP10200] where [IV00102].[ITEMNMBR] = [SOP10200].[ITEMNMBR] and [SOPTYPE] = 5) and [PRIMVNDR] = YHI group by [ITEMNMBR]) as [aggregate_table])

可能是因为当时表仍在急切加载中.有什么办法吗?任何帮助将不胜感激！

Probably because the table is still getting eager loaded at the time. Is there any way to do this? Any help would be much appreciated!

推荐答案

您将需要使用 join 而不是急于加载.

You will need to use join instead of eager loading.

在Laracasts论坛上已经存在类似的问题:此处

Similar question already exists on the Laracasts forum: here

这篇关于有没有一种方法可以从Laravel中渴望加载的表中选择字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！