使用Codeigniter的活动记录将常用值追加到行中的值

Question

我有以下表格：

Amortization Table
+----------------+--------------+--------------+--------------+
|amortization_id |  contract_id |   due_date   |    amount    |
+----------------+--------------+--------------+--------------+
| 1              | 2            | 2017-03-15   |    1000.00   |
|                |              |              |              |
| 2              | 2            | 2017-04-15   |    1500.00   |
|                |              |              |              |
| 3              | 3            | 2017-08-03   |    5000.00   |
|                |              |              |              |
| 4              | 3            | 2017-09-03   |    5000.00   |
|                |              |              |              |
| 5              | 3            | 2017-10-03   |    5000.00   |
+----------------+--------------+--------------+--------------+

Contract Table
+----------------+--------------+--------------+--------------+
|   contract_id  |  project_id  |contract_name |  person_id   |
+----------------+--------------+--------------+--------------+
| 1              | 1            | Xavier Ville | 33           |
|                |              |              |              |
| 2              | 1            | Camella Homes| 45           |
|                |              |              |              |
| 3              | 2            | Burmingham   | 61           |
+----------------+--------------+--------------+--------------+

Project Table
+----------------+-------------------+
|  project  _id  |    project_name   |
+----------------+-------------------+
| 1              | Samporna Project  | 
|                |                   | 
| 2              | Velasco Project   |
+----------------+-------------------+

Person Table
+-------------+-------------------+
|  person_id  |    person_name    |
+-------------+-------------------+
| 33          |    Glenn Henley   | 
|             |                   | 
| 45          |   Alexa Gahisan   |
|             |                   | 
| 61          |  Glynis Walters   |
+-------------+-------------------+

如何查询Codeigniter活动记录的结果为：

+-----------+-------------------+-----------------+-------------+-------+
|contract_id|  contract_name    |   project_name  | person_name |january|
+-----------+-------------------+-----------------+-------------+-------+
| 2         |   Camella Homes   | Samporna Project|Alexa Gahisan|0      |
|           |                   |                 |             |       |
| 3         |   Burmingham      | Velasco Project |Glynis Walter|0      |
+-------------+-----------------+-----------------+-------------+-------+
Continuation of table above in horizontal
+--------+-------+-------+---+----+----+--------+---------+---------+
|february| march | april |may|june|july| august |september| october |
+--------+-------+-------+---+----+----+--------+---------+---------+
| 0      | 1000  | 1500  | 0 | 0  |0   |0       |0        |0        |
|        |       |       |   |    |    |        |         |         |
| 0      | 0     | 0     | 0 | 0  |0   |5000    |5000     |5000     |
+--------+-------+-------+---+----+----+--------+---------+---------+

以使查询具有基于分配的月份的摊销量的显示。在过去的几天里，这对我来说非常具有挑战性。到目前为止，我在我的模型中使用了这段代码，它返回一个错误，说subquery返回多个。

$year=date("Y");
$this->db->select('a.*');
$this->db->select('(select c.amortization_amount from amortization c where YEAR(c.due_date) ='.$year.' and MONTH(c.due_date) = 01 and c.contract_id=a.contract_id and c.paid_up=0 and c.line_type=4) as january',FALSE);
$this->db->select('(select d.amortization_amount from amortization d where YEAR(d.due_date) ='.$year.' and MONTH(d .due_date) = 02 and d.contract_id=a.contract_id and d.paid_up=0 and d.line_type=4) as february',FALSE);
$this->db->select('(select e.amortization_amount from amortization e where YEAR(e.due_date) ='.$year.' and MONTH(e.due_date) = 03 and e.contract_id=a.contract_id and e.paid_up=0 and e.line_type=4) as march',FALSE);
$this->db->select('(select f.amortization_amount from amortization f where YEAR(f.due_date) ='.$year.' and MONTH(f.due_date) = 04 and f.contract_id=a.contract_id and f.paid_up=0 and f.line_type=4) as april',FALSE);
$this->db->select('(select g.amortization_amount from amortization g where YEAR(g.due_date) ='.$year.' and MONTH(g.due_date) = 05 and g.contract_id=a.contract_id and g.paid_up=0 and g.line_type=4) as may',FALSE);
$this->db->select('(select h.amortization_amount from amortization h where YEAR(h.due_date) ='.$year.' and MONTH(h.due_date) = 06 and h.contract_id=a.contract_id and h.paid_up=0 and h.line_type=4) as june',FALSE);
$this->db->select('(select i.amortization_amount from amortization i where YEAR(i.due_date) ='.$year.' and MONTH(i.due_date) = 07 and i.contract_id=a.contract_id and i.paid_up=0 and i.line_type=4) as july',FALSE);
$this->db->select('(select j.amortization_amount from amortization j where YEAR(j.due_date) ='.$year.' and MONTH(j.due_date) = 08 and j.contract_id=a.contract_id and j.paid_up=0 and j.line_type=4) as august',FALSE);
$this->db->select('(select k.amortization_amount from amortization k where YEAR(k.due_date) ='.$year.' and MONTH(k.due_date) = 09 and k.contract_id=a.contract_id and k.paid_up=0 and k.line_type=4) as september',FALSE);
$this->db->select('(select l.amortization_amount from amortization l where YEAR(l.due_date) ='.$year.' and MONTH(l.due_date) = 10 and l.contract_id=a.contract_id and l.paid_up=0 and l.line_type=4) as october',FALSE);
$this->db->select('(select m.amortization_amount from amortization m where YEAR(m.due_date) ='.$year.' and MONTH(m.due_date) = 11 and m.contract_id=a.contract_id and m.paid_up=0 and m.line_type=4) as november',FALSE);
$this->db->select('(select n.amortization_amount from amortization n where YEAR(n.due_date) ='.$year.' and MONTH(n.due_date) = 12 and n.contract_id=a.contract_id and n.paid_up=0 and n.line_type=4) as december',FALSE);
$this->db->from('contract a');
$this->db->group_by('a.contract_id'); 
$query = $this->db->get();
return $query->result_array();

因为我不想在我的javascript中做很多工作。我想知道是否有任何方法可以使用Codeigniter的活动记录在sql查询上做到这一点。

Answer 1

为了快速修复当前代码，您可以向每个子查询添加limit 1，如...and l.line_type=4 limit 1) as october...

您也可以使用joins重写它，因为您可以在同一个表上反复使用left join。如果每个子查询中确实有多行，则可能必须使用group by YEAR(due_date) and MONTH(due_date) and contract_id或类似的东西。