How to use CodeIgniter with FROM ( .... ) AS

Question

My query is

$this->db->query("
    SELECT table_A.*
    FROM (
        SELECT MIN(table_A.table_A_id) AS table_A_id
        FROM (
            SELECT table_A.table_b_id
            FROM table_A
            JOIN table_b ON table_b.table_b_id = table_A.table_b_id
            WHERE table_b.table_c_id = 0 AND table_A.is_correct = 'y'
            GROUP BY table_A.table_b_id
        ) AS table_b_target
        JOIN table_A ON table_A.table_b_id = table_b_target.table_b_id
        GROUP BY table_A.table_b_id
    ) AS first_row
    JOIN table_A ON table_A.table_A_id = first_row.table_A_id
    WHERE table_A.is_correct = 'y'
")->result();

I tried with this and it doesn't work.

$this->db->select("table_A.*");
$this->db->from("","AS first_row");

$this->db->group_start();
$this->db->select_min('table_A.table_A_id');
$this->db->from("","AS table_b_target");

$this->db->group_start();
$this->db->select("table_A.table_b_id");
$this->db->from("table_A");
$this->db->join("table_b", "table_b.table_b_id = table_A.table_b_id");
$this->db->where("table_b.table_c_id =", 0);
$this->db->where("table_A.is_correct =", "y");
$this->db->group_by("table_A.table_b_id");
$this->db->group_end();

$this->db->join("table_A", "table_A.table_b_id = table_b_target.table_b_id");
$this->db->group_by("table_A.table_b_id");
$this->db->group_end();

$this->db->join("table_A", "table_A.table_A_id = first_row.table_A_id");
$this->db->where("table_A.is_correct =", "y");
$result = $this->db->get()->result();

Can someone show an example of CodeIgniter with $this->db->from that searches for specific data from another query?

I tried to separate these into 3 steps (search->get result-> search for each result->get result....) but it is slower than one query.

Answer 1

try this once

$this->db->select('table_A.*');
$this->db->from('(SELECT MIN(table_A.table_A_id) AS table_A_id
                FROM (SELECT table_A.table_b_id
                      FROM table_A
                      JOIN table_b ON table_b.table_b_id = table_A.table_b_id
                      WHERE table_b.table_c_id = 0 AND table_A.is_correct = "y"
                      GROUP BY table_A.table_b_id) AS table_b_target
                JOIN table_A ON table_A.table_b_id = table_b_target.table_b_id
                GROUP BY table_A.table_b_id) AS first_row', NULL, FALSE);

$this->db->join('table_A', 'table_A.table_A_id = first_row.table_A_id');
$this->db->where('table_A.is_correct', 'y');

$result = $this->db->get()->result();

to run in codeigniter try using this code-

$query = $this->db->query('
    SELECT table_A.*
    FROM (
        SELECT MIN(table_A.table_A_id) AS table_A_id
        FROM (
            SELECT table_A.table_b_id
            FROM table_A
            JOIN table_b ON table_b.table_b_id = table_A.table_b_id
            WHERE table_b.table_c_id = 0 AND table_A.is_correct = "y"
            GROUP BY table_A.table_b_id
        ) AS table_b_target
        JOIN table_A ON table_A.table_b_id = table_b_target.table_b_id
        GROUP BY table_A.table_b_id
    ) AS first_row
    JOIN table_A ON table_A.table_A_id = first_row.table_A_id
    WHERE table_A.is_correct = "y"
');

$result = $query->result();