php - 如何在 laravel View 或 Controller 中格式化连接查询结果？

Question

我有五个表 students、grades、subjects、terms 和 scores。我正在对这些表执行内部联接以返回结果。这是我的模式的样子:

学生表:

students
--------
id *
name
class_id (fk)

主题表:

subjects
--------
id *
name

类表:

classes
--------
id *
name

术语表:

terms
--------
id *
name

分数表:

scores
---------------
id *
student_id (fk)
subject_id (fk)
class_id (fk)
term_id (fk)
score

我的 laravel 查询:

$scores = \DB::table('scores')
        ->join('students', 'students.id', '=', 'scores.student_id')
        ->join('subjects', 'subjects.id', '=', 'scores.subject_id')
        ->join('grades', 'grades.id', '=', 'scores.grade_id')
        ->join('terms', 'terms.id', '=', 'scores.term_id')
        ->select('students.first_name', 'students.surname', 'subjects.name as subject', 'grades.name as grade', 'terms.name as term', 'score')
        ->where('students.id', 1)
        ->whereBetween('scores.term_id', [1, 3])
        ->get();

当我死掉并转储它时查询返回的结果:

毫无疑问，查询返回了正确的结果，但问题是我希望结果像这样出现在我的 html 表中:

根据我现在在 Controller 和 View 中的代码，它是这样显示的。

Controller :

public function index()
{
    //
    $scores = \DB::table('scores')
        ->join('students', 'students.id', '=', 'scores.student_id')
        ->join('subjects', 'subjects.id', '=', 'scores.subject_id')
        ->join('grades', 'grades.id', '=', 'scores.grade_id')
        ->join('terms', 'terms.id', '=', 'scores.term_id')
        ->select('students.first_name', 'students.surname', 'subjects.name as subject', 'grades.name as grade', 'terms.name as term', 'score')
        ->where('students.id', 1)
        ->whereBetween('scores.term_id', [1, 3])
        ->get();


    // finding details of the student based on id pased
    $student = Student::findOrFail(1);

    // getting the name of the student
    $name = $student->first_name.' '.$student->surname;

    // getting the class or grade of the student (grade 12 or grade 11)
    $grade = $student->grade->name;

    // getting the current date
    $date = Score::date();

    return view('scores.home', compact('scores', 'name', 'date', 'grade'));

查看:

<table class="table table-bordered table-condensed table-striped">
    <thead>
        <tr>
            <th scope="row">Name</th>
            <td colspan="4">{{$name}}</td>
        </tr>
        <tr>
            <th scope="row">Class</th>
            <td colspan="2">{{$grade}}</td>

            <th scope="row">Date</th>
            <td>{{$date->toFormattedDateString()}}</td>
        </tr>
        <tr>
            <th class="text-center">Subject</th>
            @foreach($scores as $score)
                <th class="text-center">{{$score->term}}</th>
            @endforeach
        </tr>
    </thead>
    <tbody>
        @foreach($scores as $score)
            <tr>
                <td>{{$score->subject}}</td>
                <td>{{$score->score}}</td>
            </tr>
        @endforeach
    </tbody>
</table>

结果:

如上面的结果所示，术语名称 1st Period 被重复，我该如何避免这种情况？如何在 View 或 Controller 中重构我的查询或代码以获得我想要的结果？

Answer 1

首先，我会稍微简化查询，只选择您不知道的数据。无需返回每一行的学生姓名和成绩，因为它们始终相同。

$student = Student::findOrFail(1);

$scores = \DB::table('scores')
    ->join('subjects', 'subjects.id', '=', 'scores.subject_id')
    ->join('terms', 'terms.id', '=', 'scores.term_id')
    ->select('subjects.name as subject', 'terms.name as term', 'score')
    ->where('scores.student_id', $student->id)
    ->whereBetween('scores.term_id', [1, 3])
    ->get();

您将得到以下与您的结果相似的集合:

[
    0 => (object)[
        'subject' => 'Mathematics',
        'term' => '1st Period',
        'score' => 99
    ],
    1 => (object)[
        'subject' => 'Biology',
        'term' => '2nd Period',
        'score' => 99
    ],
    2 => (object)[
        'subject' => 'Biology',
        'term' => '3rd Period',
        'score' => 79
    ]
]

现在将其转换为嵌套结构:

$scores = $scores->groupBy('subject')->map(function($item){
    return $item->keyBy('term')->map(function($item){
        return $item->score;
    });
});

您将获得以下合集:

    [
        'Mathematics' => [
            '1st Period' => 99,
        ],
        'Biology' => [
            '2nd Period' => 99,
            '3nd Period' => 79
        ]
    ]

但这不是表结构——缺少一些术语。所以您需要以某种方式填写缺失的条款，因为您不想这样做。我会创建一个空表结构并将数据填充到其中:

$terms = Term::whereBetween('id', [1, 3])->pluck('name');
// returns: ['1st Period', '2nd Period', '3rd Period']

用空分数初始化表:

$scoreTable = [];
foreach ($scores->keys() as $subject){
    $scoreTable[$subject] = [];
    foreach ($terms as $term){
        $scoreTable[$subject][$term] = '';
    }
}

用给定的分数填表:

foreach ($scores as $subject => $row){
    foreach($row as $term => $score){
        $scoreTable[$subject][$term] = $score;
    }
}

现在“表格”看起来像这样:

    [
        'Mathematics' => [
            '1st Period' => 99,
            '2nd Period' => '',
            '3nd Period' => '',
        ],
        'Biology' => [
            '1st Period' => '',
            '2nd Period' => 99,
            '3nd Period' => 79,
        ],
    ]

将它传递给您的 View 并像这样呈现表格:

<table>
    <tr>
        <th>Subject</th>
        @foreach($terms as $term)
            <th>{{$term}}</th>
        @endforeach
    </tr>
    @foreach($scoreTable as $subject => $scores)
        <tr>
            <td>{{$subject}}</td>
            @foreach($terms as $term)
                <td>{{$scores[$term]}}</td>
            @endforeach
        </tr>
    @endforeach
</table>

您将获得以下 HTML 代码:

<table>
    <tr>
        <th>Subject</th>
        <th>1st Period</th>
        <th>2nd Period</th>
        <th>3rd Period</th>
    </tr>
    <tr>
        <td>Mathematics</td>
        <td>99</td>
        <td></td>
        <td></td>
    </tr>
    <tr>
        <td>Biology</td>
        <td></td>
        <td>99</td>
        <td>79</td>
    </tr>
</table>

更新:

获取 $scoreTable 的更短方法可能是

$terms = Term::whereBetween('id', [1, 3])->pluck('name');

$initRow = $terms
    ->keyBy(function($term){ return $term; })
    ->map(function(){ return ''; });

$scoreTable = $scores
    ->groupBy('subject')
    ->map(function($subject) use($initRow){
        $row = $subject
            ->keyBy('term')
            ->map(function($term) use($initRow){
                return $term->score;
            });
        return $initRow->merge($row);
    });

但是看起来可读性不是很好。也可能有一个或另一个 collection 函数将取代 map 函数并使事情变得更容易。但我不认识他们所有人。

更新 2:

这是另一种更短的方法，但在更大的数据集上可能会很慢，因为昂贵的 where() 函数会为每个表格单元格调用两次。

$scoreTable = [];
foreach ($scores->pluck('subject')->unique() as $subject){
    foreach ($scores->pluck('term')->unique() as $term) {
        $scoreTable[$subject][$term] = $scores
            ->where('subject', $subject)
            ->where('term', $term)
            ->pluck('score')
            ->first();
    }
}

更新 3:

最后我想出了以下解决方案，(我认为)这是最可读/最简单和最快速的。

$subjects = $scores->pluck('subject')->unique(); // ['Mathematics', 'Biology']
$terms    = $scores->pluck('term')->unique(); // ['1st Period', '2nd Period', '3rd Period']

$scoreTable = [];
foreach ($subjects as $subject) {
    foreach ($terms as $term) {
        $scoreTable[$subject][$term] = '';
    }
}

foreach ($scores as $row) {
    $scoreTable[$row->subject][$row->term] = $row->score;
}

return view('scores.home', compact('scoreTable', 'terms', 'name', 'date', 'grade'));

前两行将从查询结果中提取唯一的主题和术语。 (有关 pluck() 和 unique() 方法的更多信息，您将在 laravel docs 中找到。)然后在以下嵌套循环中使用它们来生成具有空值的表结构(主题 ✕ 术语)。在下一个循环中，查询结果的分数被填充到那个“表”中。