How to Implement Subquery Filtering in CodeIgniter using Active Record or the Subquery Library?

Subquery Filtering in CodeIgniter: A Query Builder Approach

The task at hand is to translate the following SQL query into CodeIgniter's active record syntax:

SELECT *
FROM certs
WHERE id NOT IN (SELECT id_cer FROM revokace);

This query retrieves all rows from the "certs" table where the "id" column is not present in the subquery that selects "id_cer" from the "revokace" table.

Active Record Implementation

CodeIgniter's query builder provides a straightforward solution:

$this->db->select('*')
         ->from('certs')
         ->where('`id` NOT IN (SELECT `id_cer` FROM `revokace`)', NULL, FALSE);

The "where()" method accepts a raw SQL string as the first argument, allowing us to incorporate the subquery directly into the main query. The second and third arguments (NULL and FALSE) prevent CodeIgniter from escaping the subquery, which would otherwise break the query.

Subquery Library Extension

Alternatively, consider utilizing the Subquery library:

$this->db->select('*')
         ->from('certs')
         ->subquery('where_in', function($subquery) {
             $subquery->select('id_cer')
                      ->from('revokace');
         }, 'id', FALSE);

This technique provides a more concise and reusable solution for incorporating subqueries into CodeIgniter queries.

The above is the detailed content of How to Implement Subquery Filtering in CodeIgniter using Active Record or the Subquery Library?. For more information, please follow other related articles on the PHP Chinese website!