[QUERY] hitung umur anggota pada propinsi - fourslickz/notes GitHub Wiki

MYSQL QUERY

SELECT 
    TIMESTAMPDIFF(YEAR, a.tanggal_lahir, CURDATE()) AS umur,
    COUNT(*) AS jumlah
FROM tm_data_anggota a
JOIN tm_data_sekolah s ON a.sekolah_id = s.id
JOIN tm_ref_kecamatan kec ON s.kecamatan_id = kec.id
JOIN tm_ref_kabupaten kab ON kec.kabupaten_id = kab.id
WHERE kab.propinsi_id = 71
AND a.tanggal_lahir >= DATE_SUB(CURDATE(), INTERVAL 71 YEAR)
AND a.tanggal_lahir < DATE_SUB(CURDATE(), INTERVAL 7 YEAR)

GROUP BY umur
ORDER BY umur;

ELOQUENT

use Modules\User\Entities\Profile;
use Illuminate\Support\Facades\DB;

$result = [];

$chunks = Profile::pluck('id')->chunk(5000);

foreach ($chunks as $ids) {

    $rows = DB::connection('pramuka')
        ->table('tm_data_anggota as a')
        ->join('tm_data_sekolah as s', 'a.sekolah_id', '=', 's.id')
        ->join('tm_ref_kecamatan as kec', 's.kecamatan_id', '=', 'kec.id')
        ->join('tm_ref_kabupaten as kab', 'kec.kabupaten_id', '=', 'kab.id')
        ->select(
            DB::raw('TIMESTAMPDIFF(YEAR, a.tanggal_lahir, CURDATE()) as umur'),
            DB::raw('COUNT(*) as jumlah')
        )
        ->where('kab.propinsi_id', 96)
        ->whereIn('a.id', $ids->toArray())
        ->whereBetween('a.tanggal_lahir', [
            now()->subYears(70),
            now()->subYears(7)
        ])
        ->groupBy('umur')
        ->get();

    foreach ($rows as $row) {
        $result[$row->umur] = ($result[$row->umur] ?? 0) + $row->jumlah;
    }
}

ksort($result);

collect($result)->map(function ($jumlah, $umur) {
    return [
        'umur' => $umur,
        'jumlah' => $jumlah
    ];
})->values();