如何将指定SQL代码转换为CodeIgniter框架代码？

Let's break down your original SQL and translate it into both CodeIgniter 3 and CodeIgniter 4's query builder syntax—this keeps your code maintainable and framework-compliant.

Original SQL Recap #

SELECT *,sum(sales_total) as daily_report FROM tbl_sales,(select sum(sales_total) as month from tbl_sales where date_format(sales_date,'%m') = '01') as totals where sales_date = '2018-01-18'

This query pulls all sales data for 2018-01-18, calculates the daily total (daily_report), and includes the total sales for the entire month of January (month) via a subquery.

CodeIgniter 3 Implementation #

Use the Active Record class with a compiled subquery:

// Load the database library (if not auto-loaded)
$this->load->database();

// Build the subquery to get January's total sales
$monthly_total_subquery = $this->db->select_sum('sales_total', 'month')
                                   ->from('tbl_sales')
                                   ->where("DATE_FORMAT(sales_date, '%m')", '01')
                                   ->get_compiled_select();

// Main query: join the subquery and calculate daily total
$this->db->select('*, SUM(sales_total) AS daily_report');
$this->db->from("tbl_sales, ($monthly_total_subquery) AS totals");
$this->db->where('sales_date', '2018-01-18');

// Execute and get results
$query = $this->db->get();
$daily_report = $query->row(); // Use row() for single result, result() for multiple

CodeIgniter 4 Implementation #

CI4's Query Builder has better support for subqueries directly in joins:

// Connect to the database
$db = \Config\Database::connect();

// Create the subquery for monthly totals
$subquery = $db->table('tbl_sales')
               ->selectSum('sales_total', 'month')
               ->where("DATE_FORMAT(sales_date, '%m')", '01');

// Build the main query with a cross join to the subquery
$query = $db->table('tbl_sales')
            ->select('*, SUM(sales_total) AS daily_report')
            ->join($subquery, '1=1', 'cross') // Cross join since subquery returns one row
            ->where('sales_date', '2018-01-18')
            ->get();

// Fetch results
$daily_report = $query->getRow(); // Use getRow() for single result, getResult() for multiple

Quick Tips #

• If your sales_date column is a DATE type, you can simplify the monthly filter to MONTH(sales_date) = 1 instead of DATE_FORMAT—this is often faster for database indexing.
• Use row()/getRow() instead of result()/getResult() if you expect only one row (which makes sense for a single day's report).

内容的提问来源于stack exchange，提问作者Leorah Sumarong