Webrevised

Learn how to extend the Code Igniter Active Record class and use it to make complex queries with mutliple ANDs and ORs

Everyone working with code igniter is undoubtedly using the Active Record Class, which allows us to generate SQL queries with out the hassle of SQL itself.

I personally love it, but sometimes I am a bit frustrated with its limitations and would really like to extend it and add my own methods, tailored to my project. By reading a bit around internet I could not find a way to simple extend the active record class. I have found various ways to do it but most were hacks, requiring you to change code igniter core files.

Observations on Code Igniter

Obviously this was not an option for me so I started digging around and came up with some interesting results.

1. The “loader” and “loader overrides” are loaded before the database class during CI initialisation. So this is where my work should take place.
2. system/database/DB driver.php and system/database/DB_active_rec.php are loaded using require once. So I can load them by hand and not risk of errors caused by multiple require calls
3. By looking at /system/database/DB.php I can see that the guys at Ellis labs will not try to initialize the CI_DB class if it’s already initialized!
4. The CI_DB class extends the DB_active_record class.

The master plan

1. Create a MY_Loader.php file (even if I’m not using my own loader) so I can hook in the CI initialisation procedure.
2. Manually require_once the DB driver.php and DB_active_rec.php files
3. Create my own version of Active record class by extending the DB_active_record class
4. Manually create the CI_DB class by extending my Active record class

Implementation

This are the contents of the MY_Loader.php file

<?php (defined('BASEPATH')) OR exit('No direct script access allowed');
//Load Active Record manually
require_once(BASEPATH.'database/DB_driver.php');
require_once(BASEPATH.'database/DB_active_rec.php');
//Load my version of Active Record 
require_once(APPPATH. 'core/Active_Record.php');
//Finally initialize the DB class
class CI_DB extends custom_active_record{} 

//In order to not break the loader class I will create my dummy loader class
class MY_Loader extends CI_Loader {}

//NOTE that if you are using HMVC you would probably do something like this
//require APPPATH."third_party/MX/Loader.php";
//class MY_Loader extends MX_Loader {}

Now, here are the contents of the DB_active_rec.php file

class My_Active_Record extends CI_DB_active_record
{
   function test_func() { echo "Custom method at your service"; }
}

Finally I am able to use the following in any part of my application

 $this->db->test_func()

which will result in:

But really, why extend the Code Igniter Active Record Class?

I would really like to be able to write complex queries using Active Record. How about this query, which would allow me to find all unpublished content items containing the word “web” in their title or in their content…

SELECT * FROM `content` WHERE (`enabled` = 0 OR `publishdate` > ’2011-11-04′)  AND (`title` LIKE ‘%web%’ OR `content` LIKE ‘%web%’)

Currently Active record does not support this (nested where cases). But wouldn’t it be neat if we could create it using AR?

$this->db->group_start()->where('enabled','0')->or_where('publishdate >',date('Y-m-d'))->group_end();
$this->db->and_();
$this->db->group_start()->like('title','web')->or_like('content','web')->group_end();
$ret = $this->db->get();

Guess what, I have mocked up a custom version of Active Record which will allow you to do just that. Use the group_start() to open a parenthesis “(” , group_end() to close a parenthesis “)” , and_() to add an ” AND ” clause , or_() to add an “OR” clause …

I have made some changes in the internals of the Active Record class so you may notice some changes in your old queries. More specific:

$this->db->from(‘users’)->where(‘status’,1)->like(‘username’,'test’)->where(‘pending_activation’,0) would produce:

SELECT * FROM `users` WHERE `status`=1 AND `pending_activation` =0 AND `username` LIKE '%test%`

But using the version I provide it will be:

SELECT * FROM `users` WHERE `status`=1 AND `username` LIKE '%test%` AND `pending_activation`=0

(notice the change in positioning of  where clauses and like clauses ?)

So before using my active record class, test test test!

Downloads

Download Custom Active Record class allowing AND OR selects (36)

Learn how to extend the Code Igniter Active Record class and use it to make complex queries with mutliple ANDs and ORs Everyone working with code igniter is undoubtedly using the Active Record Class, which allows us to generate SQL queries with out the hassle of SQL itself. I personally love it, but sometimes I am ...