Pagination with Join queries

Hello, i have problem in pagination.

im try to make manual pagination based on documentation, and get some help from ChatGPT, but that AI cannot fix it.

i can't get array key when using  select() and join() it with another table.

this is it,
my english not good, hope you all understand what i mean.

PHP Code:

public function berita() {
        $perPage = 10;
    
        // Membuat instance pager.
        $pager = service('pager');
    
        $page = (int) ($this->request->getGet('page') ?? 1);
    
        $query = $this->beritaModel
            ->select('tb_berita.*, tb_foto.foto AS foto_berita, tb_katberita.nama AS kategori_berita')
            ->join('tb_foto', 'tb_foto.id_konten = tb_berita.id AND tb_foto.tabel = "tb_berita"', 'left') 
            ->join('tb_kat_berita', 'tb_kat_berita.id = tb_berita.id_kat','left')
            ->where('tb_berita.aktif', '1')
            ->where('tb_berita.id_kat', '1')
            ->orderBy('tb_berita.id', 'desc');
    
        // Menghitung total data yang ada di tabel.
        $total = $query->countAll();
    
        $konten = $query->findAll($perPage, $page * $perPage - $perPage);
    
        foreach ($konten as &$beritaterbaru) {
            $beritaterbaru['nama_hari'] = $this->namaHari($beritaterbaru['hari']);
        }
    
        $tahunTerakhir = date('Y-m-d', strtotime('-1 year'));
        $beritapopuler = $this->beritaModel->where('aktif', '1')->where('tanggal >=', $tahunTerakhir)->orderBy('hit', 'desc')->limit(6)->find();
    
        foreach ($beritapopuler as &$databerita) {
            $databerita['nama_hari'] = $this->namaHari($databerita['hari']);
        }
    
        $pengumuman = $this->pengumumanModel->where('aktif', '1')->orderBy('id', 'desc')->limit(6)->find();
    
        foreach ($pengumuman as &$datapengumuman) {
            $datapengumuman['nama_hari'] = $this->namaHari($datapengumuman['hari']);
        }
    
        $download = $this->fileModel->where('aktif', '1')->where('id_kat', '2')->orderBy('id', 'desc')->limit(3)->find();
    
        foreach ($download as &$datadownload) {
            $datadownload['nama_hari'] = $this->namaHari($datadownload['hari']);
        }
    
        $pager_links = $pager->makeLinks($page, $perPage, $total, 'default_full');
    
        $data = [
            'konten' => $konten,
            'modul' => 'berita',
            'pager_links' => $pager_links,
            'kontak' => $this->kontakModel->where('id', 1)->first(),
        ];
    
        $data2 = [
            'beritapopuler' => $beritapopuler,
            'pengumuman' => $pengumuman,
            'download' => $download,
        ];
    
        return view('themeopi/header', $data) .
            view('themeopi/content_category', $data2) .
            view('themeopi/footer');
    } 


i try to var_dump() the result $data['konten'], but 'foto_berita' and 'kategori_berita' not exist.
look at $query,  OrderBy not work too .
where the problem?

Check the SQL statement that was executed from Debug Toolbar.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Check the SQL statement that was executed from Debug Toolbar.

I checked it, that key is not in the result

in database and vars tab,

this is screenshoot of executed queries


Not all of the queries worked

Check the SQL statement that was executed from Debug Toolbar.

I checked it, that key is not in the result

in database and vars tab,

this is screenshoot of executed queries


Not all of the queries worked

AAAAAAAAAAAAAAKHH...!!!

I finally discovered the problem by accident ....!!

it turns out that each execution will delete the builder function in the variable.

in the code seen I do countAll() before doing findAll().
it turns out countAll() reset all the contents of $query except the model.

It seems that we can no longer use the same variable for a second execution without refilling the builder function

....
after 4 days, My stress immediately disappeared. hahahaha

thank you all...