来自Eloquent查询的Array中的NULL结果

Question

谁知道为什么这个查询会产生一些空的结果(数组)：

$events = $this->event
    ->with('delegates', 'course')
    ->where('start_date', '<=', $today)
    ->where('active', '1')
    ->where('event_status_id', '!=', '3')
    ->hasCosts()
    ->has('delegates')
    ->get()
    ->map(function($event) {
        foreach ($delegates = $event->delegates()->has('contact')->get() as $delegate) {
            $account = $delegate->contact->account;

            return [
                'company' => $account->company_name,
                'income' => $account->income($delegates),
                'profit' => $account->profit($event, $delegates),
                'event' => $account->eventDetails($event, $delegates)
            ];
        }
    })
    ->toArray();

我已经进入了'foreach‘循环，并转储了每个事件、帐户、联系人和委托的id，那里没有空的结果。我也没有收到任何错误。

当我转储$events变量时，我收到如下输出：

array(8) {
    [0] NULL
    [1] array(4) {
        ["company"] "Razorfish"
        ["income"] 523
        ["profit"] "69.29"
        ["event"] "ITIL® Service Transition Apr 7, 2014 in London (141)"
    }
    [2] array(4) {
        ["company"] "European Central Bank - Europa ECB"
        ["income"] 1332
        ["profit"] "137.33"
        ["event"] "ITIL® Service Offerings & Agreements Apr 7, 2014 in London (142)"
    }
    [3] array(4) {
        ["company"] "Knowledge Pool - KP delegates"
        ["income"] 475
        ["profit"] "-111.75"
        ["event"] "ITIL® Foundation Apr 7, 2014 in Leeds (143)"
    }
    [4] array(4) {
        ["company"] "Plan International/ Plan UK"
        ["income"] 537
        ["profit"] "118.43"
        ["event"] "ITIL® Foundation Apr 14, 2014 in London (144)"
    }
    [5] array(4) {
        ["company"] "Cell Therapy Catapult ( part of Guy's hospital)"
        ["income"] 550
        ["profit"] "-114.75"
        ["event"] "ITIL® Service Design Apr 14, 2014 in London (145)"
    }
    [6] array(4) {
        ["company"] "European Central Bank - Europa ECB"
        ["income"] 597
        ["profit"] "69.80"
        ["event"] "BCS Specialist Certificate in Supplier Management Apr 14, 2014 in London (146)"
    }
    [7] array(4) {
        ["company"] "C Hoare & Co (hoares bank)"
        ["income"] 523
        ["profit"] "97.71"
        ["event"] "ITIL® Continual Service Improvement Apr 23, 2014 in London (148)"
    }
}

请注意第一个空结果。这只是输出的一个示例，但在实际输出中有许多类似的结果。

为简洁起见，初始查询中的hasCosts()方法是一个查询范围函数：

public function scopeHasCosts($query)
{
    return $query->where('tutor_cost', '>', 0)
        ->orWhere('exam_cost', '>', 0)
        ->orWhere('material_cost', '>', 0)
        ->orWhere('venue_cost', '>', 0)
        ->orWhere('notional_cost', '>', 0)
        ->orWhere('buy_price', '>', 0);
}

Answer 1

在您的map()回调中，您似乎只返回了具有联系人的委托的foreach循环的第一次迭代。如果事件没有任何具有联系人的委托，则不会返回任何内容-因此返回null。对吗？

正如文档所述：

映射方法遍历集合并将每个值传递给给定的回调。回调可以自由地修改项并返回它，从而形成已修改项的新集合

因此，您基本上将events集合的每一项替换为仅用于第一个委托的数组结果。如果您想要包含每个事件的联系人的完整代理列表，最好是迭代事件和代理，构建一个单独的数组。像这样的东西可能会起作用：

$events = $this->event
    ->with('delegates', 'course')
    ->where('start_date', '<=', $today)
    ->where('active', '1')
    ->where('event_status_id', '!=', '3')
    ->hasCosts()
    ->has('delegates')
    ->get();

$results = [];

foreach ($events as $event) {
    foreach ($delegates = $events->delegates()->has('contact')->get() as $delegate) {
        $account = $delegate->contact->account;

        $results[] = [
            'company' => $account->company_name,
            'income' => $account->income($delegates),
            'profit' => $account->profit($event, $delegates),
            'event' => $account->eventDetails($event, $delegates)
        ];
    }
}

Answer 2

正如@benJ提到的，这在一定程度上与没有联系人的代表有关。

每个代理通常都有一个联系人，但如果联系人未知，他们就会链接到一个“未知”表，该表有一个唯一的键和一个与之关联的帐户。但有时，未知者也没有账户。

我已经修改了查询以执行以下操作：

$events = $this->event
    ->has('delegates')
    ->with('delegates', 'course')
    ->where('start_date', '<=', $today)
    ->where('active', 1)
    ->whereNotIn('event_status_id', [3])
    ->hasCosts()
    ->get()
    ->map(function($event) use ($companies) {
        foreach ($delegates = $event->delegates()->has('contact')->get() as $delegate) {
            $account = $delegate->contact->account;

            return [
                'company' => $account->company_name,
                'account_manager' => $account->user->name(),
                'income' => $account->income($delegates),
                'profit' => $account->profit($event, $delegates),
                'event' => $account->eventDetails($event, $delegates)
            ];
        }

        foreach ($delegates = $event->delegates()->has('unknown')->get() as $delegate) {
            $account = $delegate->unknown->account;

            if ($account) {
                return [
                    'company' => $account->company_name,
                    'account_manager' => $account->user->name(),
                    'income' => $account->income($delegates),
                    'profit' => $account->profit($event, $delegates),
                    'event' => $account->eventDetails($event, $delegates)
                ];
            } else {
                $costPerDelegate = $event->costs() / count($event->delegates);
                $eventVenue = ( ! is_null($event->venue)) ? $event->venue->city : $event->venue_city;
                $eventDetails = ($event->course) ? $event->course->title : 'Unknown Course' . ' ' . $event->start_date->toFormattedDateString() . ' in ' . $eventVenue . ' (' . $event->id . ')';

                return [
                    'company' => 'Unknown',
                    'account_manager' => 'Unknown',
                    'income' => $delegate->price,
                    'profit' => number_format((float) $delegate->price - $costPerDelegate, 2, '.', ''),
                    'event' => $eventDetails
                ];
            }
        }
    })
    ->toArray();

这将返回我的查询的所有结果。如果有人有更干净的方法来做这件事，我很想听听。

我现在必须弄清楚如何根据公司名称组合每个结果，然后添加每个公司的收入/利润，以及在events下创建一个新数组，列出该公司的事件。