时间戳

首页 > 解决方案 > Codeigniter 查询未返回左表中的所有记录。只返回匹配的记录

php - Codeigniter 查询未返回左表中的所有记录。只返回匹配的记录

问题描述

查询仅返回联接表中的匹配记录。我究竟做错了什么？我正在尝试显示代理表中的所有记录，无论贷款表中是否存在匹配记录。也许我试图实现的逻辑是错误的。

    $select = "agents.person_id, CONCAT(people.first_name, ' ', people.last_name) as last_name, SUM(loans.referral_amount) as referral_amount, COUNT( DISTINCT loans.customer_id ) as no_customers";
            $this->db->select($select, false);
            $this->db->from('agents');
            $this->db->join('people', 'agents.person_id=people.person_id', 'LEFT');
            $this->db->join('loans', 'agents.person_id=loans.referral_agent_id AND loans.loan_status = "paid" AND loans.delete_flag = 0', 'LEFT');
            $this->db->where('agents.deleted', 0);
    return $this->db->get();



Table: agents

+-----------+---------+--+
| person_id | deleted |  |
+-----------+---------+--+
|         1 |       0 |  |
|         2 |       0 |  |
|         3 |       1 |  |
|         4 |       0 |  |
+-----------+---------+--+

Table: loans

|   | loan_status | referral_amount | referral_agent_id | delete_flag|customer_id |
|---|-------------|-----------------|-------------------|-------------|-------------|
|   | paid        | 10              | 1                 | 0           | 2           |
|   | pending     | 20              | 1                 | 0           | 2           |
|   | approved    | 30              | 3                 | 1           | 1           |


Table: people

| person_id | first_name | last_name |
|-----------|------------|-----------|
| 1         | Test       | Ken       |
| 2         | Lorem      | Ipsum     |
| 3         | Stack      | Over      |

The result I am getting

| name     | referral amount | no of customers |
|----------|-----------------|-----------------|
| Test Ken | 10              | 1               |

What I am expecting

| name        | referral amount | no of customers |
|-------------|-----------------|-----------------|
| Test Ken    | 10              | 1               |
| Lorem Ipsum | null            | null            |
| Stack Over  | null            | null            |

标签： phpmysqlcodeigniter

解决方案

首先，据我所知，您将“标准查询”与“查询生成器”混合使用，最好只使用一个（最好是查询生成器，以防切换到另一个数据库引擎）。

同样在第二个 Join 中，您正在进行“AND”比较，尽管这是有效的，但您可以尝试先使连接起作用。我认为现在正在工作，但请确保调试您的查询打印结果并根据文档修复它，

$select = "agents.person_id, CONCAT(people.first_name, ' ', people.last_name) as last_name, SUM(loans.referral_amount) as referral_amount, COUNT( DISTINCT loans.customer_id ) as no_customers, people.phone_number";

$this->db->select($select, false);
$this->db->from('agents');
$this->db->join('people', 'agents.person_id=people.person_id', 'LEFT');
$this->db->join('loans', 'agents.person_id=loans.referral_agent_id', 'LEFT'); 
$this->db->where('loans.loan_status', 'paid');
$this->db->where('loans.delete_flag', 0);
$this->db->where('agents.deleted', 0);
$this->db->get();

print_r($this->db->last_query());

（最终建议：database.column无论何时使用 Join 时，请始终在查询中使用符号，这样更容易理解并避免两个数据库具有相同名称的列时出现错误）

推荐阅读