mysql - Laravel：使用别名将其他表列加入一行

Question

我想将一个月的多人轮班时间表与这些人员数据放在一起。

这是我的桌子。

karyawans table

schedules table

我能做的最好的事情是使用withfor 循环并对其进行迭代，然后像这样手动构建我的对象

$thisMonth = Carbon::create(request()->year, request()->month);
        $firstday = $thisMonth->copy()->firstOfMonth();
        $lastday = $thisMonth->copy()->lastOfMonth();

$schedules = Karyawan::with(['schedules' => function ($query) use ($firstday, $lastday) {
            $query->whereBetween('tgl', [$firstday, $lastday]);
        }])
            ->where('id_ruang', $karyawan->id_ruang)
            ->orderBy('nik', 'asc')->get();

foreach ($schedules as $s) {
            $obj = new stdClass();
            $obj->nik = $s->nik;
            $obj->nama = $s->nama;

            for ($i = 0; $i < $last; $i++) {
                $obj->{'day' . ($i + 1)} = empty($s->schedules[$i]) ? null : $s->schedules[$i]->id_shift;
            }

            array_push($data, $obj);
        }

这是输出

{
    "nik": 1659,
    "nama": "Muhammad Nando Hidayat",
    "day1": 1,
    "day2": 1,
    "day3": 1,
    "day4": 1,
    "day5": 1,
    "day6": 1,
    "day7": 1,
    "day8": 1,
    "day9": 1,
    "day10": 1,
    "day11": 1,
    "day12": 1,
    "day13": 1,
    "day14": 1,
    "day15": 1,
    "day16": 1,
    "day17": 1,
    "day18": 1,
    "day19": 1,
    "day20": 1,
    "day21": 1,
    "day22": 1,
    "day23": 1,
    "day24": 1,
    "day25": 1,
    "day26": 1,
    "day27": 1,
    "day28": 1,
    "day29": 1,
    "day30": 1,
    "day31": 1,
  },

这就是我想要的输出。但是是否有任何 laravel 查询方法可以在不使用 for 循环的情况下实现相同的目标？只是 laravel 查询

标签： mysqlsqllaravelpostgresql