I really need help and enlighment here :-)
I have 3 views:
1st_view "vtabulasi_capaianbantu_fix":
SELECT
`a`.`tblpok_tahun` AS `tblpok_tahun`,
`a`.`refsubunit_id` AS `refsubunit_id`,
`a`.`tblpok_kodesubunit` AS `tblpok_kodesubunit`,
`a`.`tblpok_kodeunit` AS `tblpok_kodeunit`,
IF (
(`a`.`tblpok_targetkeu12` > 0),
`a`.`tblpok_targetkeu12`,
IF (
(`a`.`tblpok_targetkeu11` > 0),
`a`.`tblpok_targetkeu11`,
IF (
(`a`.`tblpok_targetkeu10` > 0),
`a`.`tblpok_targetkeu10`,
IF (
(`a`.`tblpok_targetkeu09` > 0),
`a`.`tblpok_targetkeu09`,
IF (
(`a`.`tblpok_targetkeu08` > 0),
`a`.`tblpok_targetkeu08`,
IF (
(`a`.`tblpok_targetkeu07` > 0),
`a`.`tblpok_targetkeu07`,
IF (
(`a`.`tblpok_targetkeu06` > 0),
`a`.`tblpok_targetkeu06`,
IF (
(`a`.`tblpok_targetkeu05` > 0),
`a`.`tblpok_targetkeu05`,
IF (
(`a`.`tblpok_targetkeu04` > 0),
`a`.`tblpok_targetkeu04`,
IF (
(`a`.`tblpok_targetkeu03` > 0),
`a`.`tblpok_targetkeu03`,
IF (
(`a`.`tblpok_targetkeu02` > 0),
`a`.`tblpok_targetkeu02`,
IF (
(`a`.`tblpok_targetkeu01` > 0),
`a`.`tblpok_targetkeu01`,
0
)
)
)
)
)
)
)
)
)
)
)
) AS `tblpok_targetkeu`,
IF (
(
`a`.`tblpok_targetfisik12` > 0
),
`a`.`tblpok_targetfisik12`,
IF (
(
`a`.`tblpok_targetfisik11` > 0
),
`a`.`tblpok_targetfisik11`,
IF (
(
`a`.`tblpok_targetfisik10` > 0
),
`a`.`tblpok_targetfisik10`,
IF (
(
`a`.`tblpok_targetfisik09` > 0
),
`a`.`tblpok_targetfisik09`,
IF (
(
`a`.`tblpok_targetfisik08` > 0
),
`a`.`tblpok_targetfisik08`,
IF (
(
`a`.`tblpok_targetfisik07` > 0
),
`a`.`tblpok_targetfisik07`,
IF (
(
`a`.`tblpok_targetfisik06` > 0
),
`a`.`tblpok_targetfisik06`,
IF (
(
`a`.`tblpok_targetfisik05` > 0
),
`a`.`tblpok_targetfisik05`,
IF (
(
`a`.`tblpok_targetfisik04` > 0
),
`a`.`tblpok_targetfisik04`,
IF (
(
`a`.`tblpok_targetfisik03` > 0
),
`a`.`tblpok_targetfisik03`,
IF (
(
`a`.`tblpok_targetfisik02` > 0
),
`a`.`tblpok_targetfisik02`,
IF (
(
`a`.`tblpok_targetfisik01` > 0
),
`a`.`tblpok_targetfisik01`,
0
)
)
)
)
)
)
)
)
)
)
)
) AS `tblpok_targetfisik`,
IF (
(
`a`.`tblpok_realisasikeu12` > 0
),
`a`.`tblpok_realisasikeu12`,
IF (
(
`a`.`tblpok_realisasikeu11` > 0
),
`a`.`tblpok_realisasikeu11`,
IF (
(
`a`.`tblpok_realisasikeu10` > 0
),
`a`.`tblpok_realisasikeu10`,
IF (
(
`a`.`tblpok_realisasikeu09` > 0
),
`a`.`tblpok_realisasikeu09`,
IF (
(
`a`.`tblpok_realisasikeu08` > 0
),
`a`.`tblpok_realisasikeu08`,
IF (
(
`a`.`tblpok_realisasikeu07` > 0
),
`a`.`tblpok_realisasikeu07`,
IF (
(
`a`.`tblpok_realisasikeu06` > 0
),
`a`.`tblpok_realisasikeu06`,
IF (
(
`a`.`tblpok_realisasikeu05` > 0
),
`a`.`tblpok_realisasikeu05`,
IF (
(
`a`.`tblpok_realisasikeu04` > 0
),
`a`.`tblpok_realisasikeu04`,
IF (
(
`a`.`tblpok_realisasikeu03` > 0
),
`a`.`tblpok_realisasikeu03`,
IF (
(
`a`.`tblpok_realisasikeu02` > 0
),
`a`.`tblpok_realisasikeu02`,
IF (
(
`a`.`tblpok_realisasikeu01` > 0
),
`a`.`tblpok_realisasikeu01`,
0
)
)
)
)
)
)
)
)
)
)
)
) AS `tblpok_realisasikeu`,
IF (
(
`a`.`tblpok_realisasifisik12` > 0
),
`a`.`tblpok_realisasifisik12`,
IF (
(
`a`.`tblpok_realisasifisik11` > 0
),
`a`.`tblpok_realisasifisik11`,
IF (
(
`a`.`tblpok_realisasifisik10` > 0
),
`a`.`tblpok_realisasifisik10`,
IF (
(
`a`.`tblpok_realisasifisik09` > 0
),
`a`.`tblpok_realisasifisik09`,
IF (
(
`a`.`tblpok_realisasifisik08` > 0
),
`a`.`tblpok_realisasifisik08`,
IF (
(
`a`.`tblpok_realisasifisik07` > 0
),
`a`.`tblpok_realisasifisik07`,
IF (
(
`a`.`tblpok_realisasifisik06` > 0
),
`a`.`tblpok_realisasifisik06`,
IF (
(
`a`.`tblpok_realisasifisik05` > 0
),
`a`.`tblpok_realisasifisik05`,
IF (
(
`a`.`tblpok_realisasifisik04` > 0
),
`a`.`tblpok_realisasifisik04`,
IF (
(
`a`.`tblpok_realisasifisik03` > 0
),
`a`.`tblpok_realisasifisik03`,
IF (
(
`a`.`tblpok_realisasifisik02` > 0
),
`a`.`tblpok_realisasifisik02`,
IF (
(
`a`.`tblpok_realisasifisik01` > 0
),
`a`.`tblpok_realisasifisik01`,
0
)
)
)
)
)
)
)
)
)
)
)
) AS `tblpok_realisasifisik`,
IF (
(
`a`.`tblpok_realisasikeunominal12` > 0
),
`a`.`tblpok_realisasikeunominal12`,
IF (
(
`a`.`tblpok_realisasikeunominal11` > 0
),
`a`.`tblpok_realisasikeunominal11`,
IF (
(
`a`.`tblpok_realisasikeunominal10` > 0
),
`a`.`tblpok_realisasikeunominal10`,
IF (
(
`a`.`tblpok_realisasikeunominal09` > 0
),
`a`.`tblpok_realisasikeunominal09`,
IF (
(
`a`.`tblpok_realisasikeunominal08` > 0
),
`a`.`tblpok_realisasikeunominal08`,
IF (
(
`a`.`tblpok_realisasikeunominal07` > 0
),
`a`.`tblpok_realisasikeunominal07`,
IF (
(
`a`.`tblpok_realisasikeunominal06` > 0
),
`a`.`tblpok_realisasikeunominal06`,
IF (
(
`a`.`tblpok_realisasikeunominal05` > 0
),
`a`.`tblpok_realisasikeunominal05`,
IF (
(
`a`.`tblpok_realisasikeunominal04` > 0
),
`a`.`tblpok_realisasikeunominal04`,
IF (
(
`a`.`tblpok_realisasikeunominal03` > 0
),
`a`.`tblpok_realisasikeunominal03`,
IF (
(
`a`.`tblpok_realisasikeunominal02` > 0
),
`a`.`tblpok_realisasikeunominal02`,
IF (
(
`a`.`tblpok_realisasikeunominal01` > 0
),
`a`.`tblpok_realisasikeunominal01`,
0
)
)
)
)
)
)
)
)
)
)
)
) AS `tblpok_realisasikeunominal`,
`b`.`refsubunit_nama` AS `tblpok_uraian`
FROM
(
`vtblpok` `a`
JOIN `refsubunit` `b` ON (
(
`b`.`refsubunit_id` = `a`.`refsubunit_id`
)
)
)
WHERE
(
`a`.`tblpok_statusentry` = 'entryTargetRealisasi'
)
2nd_view "vtabulasi_capaianbantu":
SELECT
`a`.`tblpok_tahun` AS `tblpok_tahun`,
`a`.`refsubunit_id` AS `refsubunit_id`,
`a`.`tblpok_kodesubunit` AS `tblpok_kodesubunit`,
`a`.`tblpok_kodeunit` AS `tblpok_kodeunit`,
IF (
(`a`.`tblpok_targetkeu` > 100),
100,
`a`.`tblpok_targetkeu`
) AS `tblpok_targetkeu`,
IF (
(
`a`.`tblpok_targetfisik` > 100
),
100,
`a`.`tblpok_targetfisik`
) AS `tblpok_targetfisik`,
IF (
(
`a`.`tblpok_realisasikeu` > 100
),
100,
`a`.`tblpok_realisasikeu`
) AS `tblpok_realisasikeu`,
IF (
(
`a`.`tblpok_realisasifisik` > 100
),
100,
`a`.`tblpok_realisasifisik`
) AS `tblpok_realisasifisik`,
`a`.`tblpok_realisasikeunominal` AS `tblpok_realisasikeunominal`,
`a`.`tblpok_uraian` AS `tblpok_uraian`
FROM
`vtabulasi_capaianbantu_fix` `a`
3rd_view "get avg and sum":
SELECT
`a`.`refsubunit_id` AS `refsubunit_id`,
`a`.`tblpok_kodesubunit` AS `tblpok_kodesubunit`,
`a`.`tblpok_kodeunit` AS `tblpok_kodeunit`,
`a`.`tblpok_uraian` AS `tblpok_namasubunit`,
avg(`a`.`tblpok_realisasikeu`) AS `tblpok_realisasikeu`,
sum(
`a`.`tblpok_realisasikeunominal`
) AS `tblpok_realisasikeunominal`,
(
SELECT
avg(`b`.`tblpok_realisasikeu`)
FROM
`vtabulasi_capaianbantu` `b`
) AS `tblpok_realisasikeu_allskpd`,
(
SELECT
sum(`c`.`tblpok_apbdmurni`)
FROM
`vtabel_tblpok_level2_by_subunitid` `c`
) AS `tblpok_apbdmurni_allskpd`,
(
SELECT
sum(`c`.`tblpok_apbdperubahan`)
FROM
`vtabel_tblpok_level2_by_subunitid` `c`
) AS `tblpok_apbdperubahan_allskpd`,
(
SELECT
sum(
`b`.`tblpok_realisasikeunominal`
)
FROM
`vtabulasi_capaianbantu` `b`
) AS `tblpok_realisasikeunominal_allskpd`,
`c`.`tblpok_apbdmurni` AS `tblpok_apbdmurni`,
`c`.`tblpok_apbdperubahan` AS `tblpok_apbdperubahan`
FROM
(
`vtabulasi_capaianbantu` `a`
JOIN `vtabel_tblpok_level2_by_subunitid` `c` ON (
(
`a`.`refsubunit_id` = `c`.`refsubunit_id`
)
)
)
GROUP BY
`a`.`refsubunit_id`
At present I already used this view which is called from this model:
model:
$this->db->select("*");
$query = $this->db->get('3rd_view');
return $query->result_array();
Everything is ok. All passed good til the MVC->view loops for tables. And now the problem is:
- I can't alter the view query "where clause" because I need to query this data year filter (
a.tblpok_tahun) in 1st_view. Any opinion/solution? - I can't control 1st_view IF iteration (12 to 1 field ), because I can not pass php var to 1st view query. (For example if I need to filter the data from month 1 to 4, in my php form, theres already combo box for months, I choose 4). Then I need to restrain the nested IF from 1 to 4 nests only. How do we do it?
- I'm afraid there is some performance degradation using many combined view with alot of IF process inside. (Loading time 8s)
- Should I find another querying strategy? Should it be heavily coded in mysql or php side?
Thanks for the responds.
