phpcodeigniterjoinsubqueryquery-builder

COUNT() records from JOINed subqueries GROUPed BY year and month with CodeIgniter's query builder

I cannot find any reference on joining multiple selects like the query below in CodeIgniter's query builder. How can this be written with query builder methods?

SELECT
    a1.act,
    a1.date,
    a2.clos
FROM (
    SELECT
        COUNT(AccountActiveDate) act,
        DATE_FORMAT(AccountActiveDate, '%Y-%m') date
    FROM
        customert cust
    GROUP BY
        YEAR(AccountActiveDate),
        MONTH(AccountActiveDate) 
) a1
join (
    SELECT
        COUNT(AccountClosedDate) clos,
        DATE_FORMAT(AccountClosedDate, '%Y-%m') date
    FROM
        customert cust2
    GROUP BY
        YEAR(AccountClosedDate),
        MONTH(AccountClosedDate) 
) a2 ON a1.date = a2.date
Solution

  • You can put you sql string like this:

    $this->db->query('YOUR QUERY HERE')

    Example:

    $this->db->query('SELECT * FROM tbl_users WHERE age > 18');