How to Perform SELECT Queries with Subquery Exclusion Using CodeIgniter\'s Query Builder?

Utilizing CodeIgniter's Query Builder for SELECT Queries with Subquery Exclusion

CodeIgniter offers a versatile query builder that facilitates the creation of complex SQL queries in PHP. One such query is the SELECT query with a subquery exclusion. This query allows you to retrieve rows from a table based on a condition that excludes specific rows from another table, as expressed in the following SQL statement:

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

To replicate this query using CodeIgniter's query builder, you can leverage the where() method. This method accepts a string argument that represents the query condition:

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

In this code, the ,NULL,FALSE arguments in the where() method prevent CodeIgniter from escaping the query, ensuring its proper execution.

Alternatively, you can employ the subquery library to simplify the process further:

$this->db->select('*')
         ->from('certs');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('id_cer')
    ->from('revokace');
$this->subquery->end_subquery('id', FALSE);

By utilizing CodeIgniter's query builder and its where() or subquery methods, you can effortlessly execute complex SELECT queries with subquery exclusions in your PHP applications.

The above is the detailed content of How to Perform SELECT Queries with Subquery Exclusion Using CodeIgniter's Query Builder?. For more information, please follow other related articles on the PHP Chinese website!