Codeigniter's Model with QueryBuilder JOIN query

Question

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

Answer 1

Query builders have their limits. That's why the query method exists. If you have a complex query I'd advise you to just use $this->query();.
It will make you lose less time and effort converting something you know already works. And in the top of that, while converting complex queries you usually end up using the query builder but with big part of your SQL in it.

In your model extending CodeIgniter\Model :

    $query = $this->db->query("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");

    // your array result
    $result_array = $query->getResultArray();
    // your object result
    $result_object = $query->getResult();