Posted inCodeigniter / Programming

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.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype