php - Codeigniter 中生成的奇怪查询

Question

下面是我的代码，我从三个模型调用三个方法来检索计数，如下所示。

    $this->load->model('orders_model');
    $order_count = $this->orders_model->count_orders(array("executive_id" => $this->id));

    $this->load->model('activities_model');
    $activity_count = $this->activities_model->count_activities(array("users_id" => $this->id));

    $this->load->model('leads_model');
    $leads_count = $this->leads_model->count_leads(array("users_id" => $this->id));

这是我得到的查询:

SELECT COUNT(*) AS numrows FROM orders, activities, leads WHERE executive_id = '5' AND users_id = '5' AND users_id = '5'

导致数据库错误

为什么会这样？

订单模型

class Orders_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function get_orders($order_id = FALSE) {
        if ($order_id === FALSE) {
            $query = $this->db->get('orders');
            return $query->result();
        }
        $this->db->where('id', $order_id);
        $query = $this->db->get('orders');
        return $query->result();
    }

    public function add_order($order_data = FALSE) {
        if (!$order_data === FALSE) {
            if (is_array($order_data)) {
                return $this->db->insert('orders', $order_data);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function update_order($order_update_data = FALSE, $order_update_condition = FALSE) {
        if (!($order_update_data === FALSE && $order_update_condition === FALSE)) {
            if (is_array($order_update_data) && is_array($order_update_condition)) {
                return $this->db->update('orders', $order_update_data, $order_update_condition);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function get_custom_orders($order_custom_condition = FALSE) {
        if (!$order_custom_condition === FALSE) {
            if (is_array($order_custom_condition)) {
                #echo "Yes a parameter is passed which is also an array";
                $this->db->where($order_custom_condition);
                $query = $this->db->get('orders');
                return $query->result();
            }
        }
    }

    public function get_last_ref_id() {
        $query = $this->db->query('select sprx_ref_id from orders where id in (select max(id) from orders)');
        foreach ($query->result() as $row) {
            return $row->sprx_ref_id;
        }
    }

    public function fetch_orders($limit, $start, $order_custom_condition) {
        $this->db->limit($limit, $start);
        $this->db->order_by("id", "desc");
        $this->db->where($order_custom_condition);
        $query = $this->db->get();
        return $query->result();
    }

    public function count_orders($order_custom_condition) {
        $this->db->where($order_custom_condition);
        return $this->db->count_all_results('orders', FALSE);
    }

}

事件模型

class Activities_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function get_activities($activity_id = FALSE) {

        if ($activity_id === FALSE) {
            $query = $this->db->get('activities');
            return $query->result();
        }
        $this->db->where('id', $activity_id);
        #$this->db->order_by('id','ASC');
        $query = $this->db->get('activities');
        return $query->result();
    }

    public function add_activity($activity_data = FALSE) {
        if (!$activity_data === FALSE) {
            if (is_array($activity_data)) {
                return $this->db->insert('activities', $activity_data);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function update_activity($activity_update_data = FALSE, $activity_update_condition = FALSE) {
        if (!($activity_update_data === FALSE && $activity_update_condition)) {
            if (is_array($activity_update_data) && is_array($activity_update_condition)) {
                return $this->db->update('activities', $activity_update_data, $activity_update_condition);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function get_custom_activities($activity_custom_condition = FALSE) {
        if (!$activity_custom_condition === FALSE) {
            if (is_array($activity_custom_condition)) {
                #echo "Yes a parameter is passed which is also an array";
                $this->db->where($activity_custom_condition);
                $query = $this->db->get('activities');
                return $query->result();
            }
        }
    }

    public function fetch_activities($limit, $start, $custom_condition) {
        $this->db->limit($limit, $start);
        $this->db->order_by("id", "desc");
        $this->db->where($custom_condition);
        $query = $this->db->get();
        return $query->result();
    }

    public function count_activities($custom_condition) {
        $this->db->where($custom_condition);
        return $this->db->count_all_results('activities', FALSE);
    }

}

线索模型

class Leads_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    public function get_leads($lead_id = FALSE) {
        if ($lead_id === FALSE) {
            $query = $this->db->get('leads');
            return $query->result();
        }
        $this->db->where('id', $lead_id);
        $query = $this->db->get('leads');
        return $query->result();
    }

    public function add_lead($lead_data = FALSE) {
        if (!$lead_data === FALSE) {
            if (is_array($lead_data)) {
                return $this->db->insert('leads', $lead_data);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function update_lead($lead_update_data = FALSE, $lead_update_condition = FALSE) {
        if (!($lead_update_data === FALSE && $lead_update_condition)) {
            if (is_array($lead_update_data) && is_array($lead_update_condition)) {
                return $this->db->update('leads', $lead_update_data, $lead_update_condition);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function get_custom_leads($lead_custom_condition = FALSE) {
        if (!$lead_custom_condition === FALSE) {
            if (is_array($lead_custom_condition)) {
                #echo "Yes a parameter is passed which is also an array";
                $this->db->where($lead_custom_condition);
                $query = $this->db->get('leads');
                return $query->result();
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    public function fetch_leads($limit, $start, $lead_custom_condition) {
        $this->db->limit($limit, $start);
        $this->db->order_by("id", "desc");
        $this->db->where($lead_custom_condition);
        $query = $this->db->get();
        return $query->result();
    }

    public function count_leads($lead_custom_condition) {
        $this->db->where($lead_custom_condition);
        return $this->db->count_all_results('leads', FALSE);

    }

}

Answer 1

据我所知，您很惊讶为什么查询构建器使用来自先前查询的附加参数。

You've to reset your Query according to docs

这意味着你所有的“count_”函数应该是这样的

public function count_leads($lead_custom_condition) {
    $this->db->where($lead_custom_condition);
    return $this->db->count_all_results('leads');

}

显然你确实故意设置了错误标志 - 但我不确定为什么 ;)