By default Codeigniter didn’t give any option to use subqueries in active record. So today we go to learn how to use subqueries. For this idea, i had a search more than a day to find one library to use subquery in codeigniter active record.
It works in CodeIgniter’s (1.7.x – 2.0.2) active record class
You can use following statements in your subquery
- UNION ALL
- You can have subqueries inside subqueries inside UNION’s and UNION’s inside subqueries
Usage and Examples:
Consider the following Query Example:
SELECT username,name,id,(SELECT name FROM roles WHERE users.id = roles.user_id) as role FROM users WHERE id = 1
Active record query
$sub = $this->subquery->start_subquery('select');
$sub->where('users.id = roles.id');
Note: if you want to use this library in codeigniter 2.1.X then you needs to change some codes in your core files.
Refer library URL : https://github.com/NTICompass/CodeIgniter-Subqueries
Library code: https://raw.github.com/NTICompass/CodeIgniter-Subqueries/master/libraries/Subquery.php
Please don’t forget to share and subscribe to latest updates of the blog.