How to Write SubQuery in CodeIgniter Active Record?

Today, We want to share with you subquery in codeigniter.In this post we will show you get value from subquery in CodeIgniter, hear for subquery in codeigniter join we will give you demo and example for implement.In this post, we will learn about Sql Subquery Multiple Tables Example with an example.

A subquery is a query that is nested inside another query. In CodeIgniter, you can use the Active Record class to build subqueries.

Here’s an example of how to use subqueries in CodeIgniter:

$subquery = $this->db->select('SUM(sales) as total_sales')
                    ->from('orders')
                    ->where('status', 'complete')
                    ->get_compiled_select();

$this->db->select('name, email, ('.$subquery.') as sales')
         ->from('users')
         ->where('status', 'active')
         ->order_by('sales', 'desc');

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

In this example, the subquery calculates the total sales from completed orders in the “orders” table. The main query selects the name, email, and total sales for all active users in the “users” table, and orders the results by total sales in descending order.

The get_compiled_select() method is used to retrieve the subquery as a string, which is then concatenated with the main query using string interpolation.

You can use this approach to build more complex queries that require subqueries in CodeIgniter.

codeigniter join select field

SubQuery in CodeIgniter

$this->db->select('MemberID');
$this->db->from('Upcommings');
$sub_query = $this->db->get_compiled_select();

$this->db->select('MemberID, MemberName, CartName');
$this->db->from('Members');
$this->db->where("MemberID IN ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT MemberID, MemberName, CartName
// FROM Members
// WHERE MemberID IN (SELECT MemberID FROM Upcommings);

Next is second example of query igniter subquery which picks up all member records with salary greater than that of average salary.

$this->db->select_avg('Age');
$this->db->from('Members');
$sub_query = $this->db->get_compiled_select();

$this->db->select('*');
$this->db->from('Members');
$this->db->where("Age > ($sub_query)");
$query = $this->db->get()->result();

// Produces SQL:
// SELECT * 
// FROM Members 
// WHERE Age > (SELECT AVG(Age) FROM Members);

query with active record methods

SELECT * FROM members WHERE id IN(SELECT id FROM members_backup); 

Sub Query

// Sub Query
$this->db->select('id')->from('members_backup');
$subQuery =  $this->db->get_compiled_select();

// Main Query
$this->db->select('*')
         ->from('members')
         ->where("id IN ($subQuery)", NULL, FALSE)
         ->get()
         ->result();

Subquery In Codeigniter Active Records

$this->db->select('*')->from('Members');
$this->db->where('id NOT IN (SELECT id FROM users_master where is_active = "1")', NULL, FALSE);

I hope you get an idea about subquery in codeigniter.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.