如何将SQL查询转换为CodeIgniter查询

Question

有人可以帮我转换这个SQL查询吗？

SELECT *
FROM customer c
LEFT JOIN customer_order co 
ON c.customer_number = co.customer_number 
AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
AND c.order_status = 'unserve'
AND co.cus_ord_no IS null

将查询语句转换为 Codeigniter 查询语句，示例如下图所示：

- AgentRed

---

可能是如何在CodeIgniter中执行我的SQL查询的重复问题。 - sad saddest

Answer 1

您可以做的事情

public function view_customers()
{
    $sql = "SELECT * FROM customer c LEFT JOIN customer_order co ON c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared') WHERE c.customer_status='unpaid' AND c.order_status = 'unserve' AND co.cus_ord_no IS null";

    return $this->db->query($sql)->result();
}

您可以使用row()方法将结果输出为一个object，或者使用row_array()方法将结果输出为一个array。如果需要多个objects，则可以使用result()方法；如果需要多个arrays，则可以使用result_array()方法。

通常我的做法是这样的：

控制器：

public function view()
{
    $this->load->model('My_Model');

   $data = new stdclass;
   $data->user_lists = $this->my_model->view_users(array('nationality'=>'AMERICAN'));
}

型号：

public function view_users($param = null) //no value passed
{
   $condition = '1';
   if (!empty($param)) { //Having this will trap if you input an array or not
     foreach ($param as $key=>$val) {
       $condition .= " AND {$key}='{$val}'"; //Use double quote so the data $key and $val will be read.
     }
   }

   $sql = "SELECT * FROM users WHERE {$condition}"; //Use double quote so the data $condition will be read.
   // Final out is this "SELECT * FROM users WHERE 1 AND nationality='AMERICAN'";

   return $this->db->query($sql)->result();
}

- GGw

Answer 2

如果查询语句没有需要根据条件改变的子句，则使用$this->db-query()是正确的方法。

$sql = "SELECT * FROM customer c LEFT JOIN customer_order co 
ON c.customer_number=co.customer_number AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid' AND c.order_status='unserve' AND co.cus_ord_no IS null";

$query = $this->db->query($sql)->result();
echo json_encode($query);

在调用query()后最好加上一项检查，因为如果它失败（返回false），那么对result()的调用将会抛出异常。可以通过以下方式进行处理。

$query = $this->db->query($sql);
if($query !== FALSE) 
{
    echo json_encode($query->result());
    return;
}

echo json_encode([]); // respond with an empty array

查询生成器（QB）是一个不错的工具，但通常过于复杂。它增加了很多开销来创建一个字符串，该字符串直接传递给 $db->query()。如果您知道字符串并且没有必要重构它，那么您不需要使用 QB。

当您想有条件地更改查询语句时，QB非常有用。排序可能是其中一种可能情况。

if($order === 'desc'){
   $this->db->order_by('somefield','DESC'); 
} else {
       $this->db->order_by('somefield','ASC'); 
}

$results = $this->db
    ->where('other_field', "Foo")
    ->get('some_table')
    ->result();

因此，如果$order的值为'desc'，则查询语句将会是：

SELECT * FROM some_table WHERE other_field = 'Foo' ORDER BY somefield 'DESC' 

但如果您坚持使用查询构建器，我相信这是您的答案。

$query = $this->db
    ->join('customer_order co', "c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared')", 'left')
    ->where('c.customer_status','unpaid')
    ->where('c.order_status','unserve')
    ->where('co.cus_ord_no IS NULL')
    ->get('customer c');

//another variation on how to check that the query worked
$result = $query ? $query->result() : [];
echo json_encode($result);

- DFriend