我如何将Postgres查询到查询构建器格式codeigniter

Question

我想从许多表中选择数据，这是代码如何转动这个

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'

进入这种格式的某种态度

            $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

您不远 - 尝试以下：

// 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()

请注意，如果您的Postgresl使用((也可以将内部删除(：

，可以改进此查询：

$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()