Convert raw SQL with nest subqueries to CodeIgniter query builder methods

Question

Was looking for some help and found some but nothing for when the FROM also is a subquery.

SELECT COUNT(*) 
  FROM 
    ( SELECT tc.*, 
             ( SELECT status FROM test_case_executions tce 
                   WHERE tce.test_case_id = tc.id 
                   ORDER BY tce.execution_date DESC, tce.id DESC LIMIT 1 
             ) AS last_status FROM test_cases tc 
    ) a 
WHERE a.last_status = '$status'

Is there a way in CI to just use this and execute it or can someone help me write this in the way CI wants it?

There is codeigniter subquery also. But its little bit complicated. — Anusha
– Anusha, Commented Oct 10, 2013 at 4:17

Robin Castlin · Accepted Answer · 2013-10-10 08:20:36Z

1

Everything you need can really be found here, as mentioned in above comments. Just to get you started, here's how you could do it:

$this->db->query("
SELECT COUNT(*) AS amount
FROM (  SELECT tc.*,
        (   SELECT status
            FROM test_case_executions AS tce 
            WHERE tce.test_case_id = tc.id 
            ORDER BY tce.execution_date DESC, tce.id DESC
            LIMIT 1) AS last_status
        FROM test_cases AS tc 
    ) AS a 
WHERE a.last_status = ?
", array($status));

Basicly this is what the comments are saying. What makes this more "CI convenient" than simple mysql_query etc. is that you're escaping passed values to free yourself from errors and sql injections. Note the last part ? and the second parameter array($status). I also styled this query to be a bit easier on the eye (imo).

You might think "But I wanna use Active Records! D:", however more advanced stuff requires you to leave the comfort zone. Good luck!

answered Oct 10, 2013 at 8:20

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jrock2004 Over a year ago

Oh, I fine with going back to a move conventional way of doing it. Truth of the matter I still have not even gotten to the more difficult SQL queries. Thaanks

mickmackusa · Accepted Answer · 2024-09-09 22:59:37Z

I disagree with @Anusha that building this nested query structure is "complicated". Just build out from the innermost subquery using query builder methods, and insert the built query into its parent query as needed.

Instead of merely converting your posted SQL to query builder calls, I recommend tweaking your SQL to avoid subqueries in a SELECT clause -- these tend to be poor performers.

I recommend JOINing a subquery which groups test cases by id and returns there latest execution date, then JOIN executions table to that derived table to be able to access the last_status of each test_case, filter them by the passed-in status, and count the total.

public function countTestCasesByStatus(string $status): int
{
    $latestExecutionPerId = $this->db
        ->select('tc.id, MAX(tce.execution_date) last_execution_date')
        ->join('test_case_executions tce1', 'tc.id = tce.test_case_id')
        ->group_by('tc.id')
        ->get_compiled_select('test_cases tc');

    return $this->db
        ->join('test_case_executions tce2', 'lepi.id = tce2.test_case_id AND lepi.last_execution_date = tce2.execution_date')
        ->where('tce2.status', $status)
        ->count_all_results("($latestExecutionPerId) lepi");
}

Rendered SQL (assuming the input string is foo):

SELECT COUNT(*) AS `numrows`
FROM (
    SELECT `tc`.`id`, MAX(tce.execution_date) last_execution_date
    FROM `test_cases` `tc`
    JOIN `test_case_executions` `tce1` ON `tc`.`id` = `tce`.`test_case_id`
    GROUP BY `tc`.`id`
) lepi
JOIN `test_case_executions` `tce2` ON `lepi`.`id` = `tce2`.`test_case_id` AND `lepi`.`last_execution_date` = `tce2`.`execution_date`
WHERE `tce2`.`status` = 'foo'

Collectives™ on Stack Overflow

Convert raw SQL with nest subqueries to CodeIgniter query builder methods

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related