带有QueryBuilder JOIN查询的Codeigniter模型 [英] Codeigniter's Model with QueryBuilder JOIN query

问题描述

与CI4的模型一起使用时真的很陌生，并且努力使我现有的MySQL JOIN查询适应其用户指南中的示例.

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();
    }

工作正常.我看了一些示例，发现可以添加代码-> table('shop a')，它仍然有效，但是我还需要添加以下JOIN语句:

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

一旦我添加-> join('shop b'，'a.gtin = b.gtin and a.sale_price = b.sale_price')，我就会得到一个'404-File未找到"错误.

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.

当我查看所有CI4连接示例并调整我的代码以使其适合时，我的 foreach($ shop as $ row)循环会生成"Whoops ..."错误，因为它们以 getResult()或 getResultArray -代替 findAll().

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().

前进的方向是什么，我是否需要更改 foreach 循环.

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

完整的MySQL语句:

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

推荐答案

查询构建器有其局限性.这就是查询方法存在的原因.如果您有复杂的查询，建议您只使用 $ this-> query(); .
这将使您减少浪费的时间和精力来转换您已经知道的东西.最重要的是，在转换复杂的查询时，您通常最终会使用查询生成器，但其中包含很大一部分SQL.

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.

在您的模型中，扩展 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();

这篇关于带有QueryBuilder JOIN查询的Codeigniter模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！