Really new to working with CI4's Model and struggling to adapt my existing MySQL JOIN queries to work with the examples in its User Guide.

I have adapted part of my code like so:

    public function brand_name($brand_name_slug)
    {
        return $this->asArray()
                    ->where('availability', 'in stock')
                    ->where('sku !=', '')
                    ->where('brand_name_slug', $brand_name_slug)
                    ->groupBy('gtin')
                    ->orderBy('brand_name, subbrand_name, product, size, unit')
                    ->findAll();
    }

It works fine. I have looked at examples, and figured out I can add the code ->table('shop a') and it still works, but I also need to to add the following JOIN statement:

JOIN (SELECT gtin, MIN(sale_price) AS sale_price FROM shop GROUP BY gtin) AS b ON a.gtin = b.gtin AND a.sale_price = b.sale_price

As soon as I add ->join('shop b', 'a.gtin = b.gtin and a.sale_price = b.sale_price') I get a '404 - File Not Found' error.

When I look at all examples of CI4 joins and adapt my code to fit, my foreach($shop as $row) loop generates a 'Whoops...' error because they end with a getResult() or getResultArray - instead of findAll().

Which is the way forward, and do I need to change my foreach loop.

Full MySQL statement:

SELECT * FROM shop a JOIN (SELECT gtin, MIN(sale_price) AS sale_price FROM shop GROUP BY gtin) AS b ON a.gtin = b.gtin AND a.sale_price = b.sale_price WHERE availability = 'in stock' AND sku != '' AND brand_name_slug = $brand_name_slug GROUP BY gtin ORDER BY brand_name, subbrand_name, product, size