How i turned postgres query to query builder format codeigniter?

Question

i want to select datas from many tables here is the code how to turned this

SELECT a.*, b.penyusun, c.keywords 
                FROM cb_monograf a 
                INNER JOIN (
                select row_number() over (order by id_monograf) nomer, id_monograf, string_agg(id_penyusun::varchar,'-') penyusun
                  from cb_penyusun_monograf
                 group by id_monograf
                ) b ON a.id_monograf = b.id_monograf
                INNER JOIN (
                select row_number() over (order by id_monograf) nomer, id_monograf, string_agg(id_keywords::varchar,'-') keywords
                  from cb_keywords_monograf
                 group by id_monograf
                ) c ON a.id_monograf = c.id_monograf
                WHERE a.jenis = 'buku'

into somekind of this format

            $this->db->select('a.*,b.penyusun')
                ->from('cb_monograf a')
                ->join("($subquery1) b","a.id_monograf = b.id_monograf","inner")
                ->where('jenis', $param_type);

Answer 1

You're not far off - try this:

// Just to keep code a bit clearer
$db = $this->db;

// Firstly build the selects
$db->select('row_number() over (order by id_monograf) nomer, id_monograf, string_agg(id_penyusun::varchar,'-') penyusun')
$db->group_by('id_monograf');
$q1 = $db->get_compiled_select('cb_penyusun_monograf');

$db->select('row_number() over (order by id_monograf) nomer, id_monograf, string_agg(id_keywords::varchar,'-') keywords');
$db->group_by('id_monograf');
$q2 = $db->get_compiled_select('cb_keywords_monograf');

// Final query
$db->select('a.*, b.penyusun, c.keywords');
$db->join("($q1) b",'a.id_monograf = b.id_monograf','inner');
$db->join("($q2) c",'a.id_monograf = c.id_monograf','inner');
$db->where('a.jenis','buku');
$data = $db->get('cb_monograf a')->result_array(); // or row_array()

Note this query can be improved if your postgresl supports USING() (and inner can probably be dropped as well) to this:

$db->select('a.*, b.penyusun, c.keywords');
$db->join("($q1) b",'id_monograf'); // inner may also not be required
$db->join("($q2) c",'id_monograf'); // inner may also not be required
$db->where('a.jenis','buku');
$data = $db->get('cb_monograf a')->result_array(); // or row_array()