Codeigniter Active Record Class Overview

In this article, I will give a small overview of the Codeigniter Active Record class, show the basic way to use it, its drawbacks and potential solution of a common problem(union operation with it, which isn’t directly supported) that developers face often. I will be using a few possible PHP code examples for demonstration purposes also, wherever applicable.

So, you are working with database functionality in Codeginiter framework. So, I guess, you already know some basics? If yes, go ahead, no problem. But if your answer is ‘NO’? Why don’t walk through a codeigniter basic application development tutorial first? It will make your life easier for doing the exercises of this tutorial.

What is CodeIgniter Active record class:

Among many others, the “Active record class” is one of the most popular and useful features of Codeigniter. It reduces the developer’s effort to create and execute SQL queries on ‘model’ section. Instead of writing query syntax, here we have to call functions with proper parameters sequentially, which, when executed, results in a full-length SQL query and returns corresponding database results. This is a great help, as while writing ourselves, sometimes, for long/complex queries, we have to correct SQL syntax several times before getting it functional.

Using This Class:

Using this active record class in a Codeigniter application is quite easy. Remember not to use this on the controller or view section(not even in library/plugin/helpers also) as this actually belongs to the model section(data access layer). Here are few PHP code samples are given below:

function get_result($id){
    //For selecting one or more columns
    $this->db->select('title, content, date');
    //For determine one or more tables to select from
    $this->db->from('table1');
    //For joining with another table, table name as first argument and condition string as second argument
    $this->db->join('table2', 'comments.id = blogs.id');
    //assign where condition
    $this->db->where('id', $id);
    //function without any argument. It actually runs the query that was built on last few statements.
    $this->db->get();
    //returns result objects array
    return $query->result();
}
Code language: PHP (php)

Note that, on ‘where’ method, you can pass an associative array as parameters as well. That will assume an ‘AND’ operator in every key-value (key = DB column name, value = result to be checked against) pair and construct the query accordingly.
Inserting data is also quite easy, as follows:

$data = array(
               'id' => "8",
               'title' => "test title");
$this->db->insert('table_name', $data);
Code language: PHP (php)

For more details references on exactly which functions are allowed, and what parameters with details Explanation with an example, please visit official documentation on it.

Do you know? How to see what query is actually getting constructed? You can get it by using “$this->db->last_query()” method after execution. We are gonna use it soon, keep reading. Also, if you keep your profiler enabled by “$this->output->enable_profiler(TRUE);”, then you should see the executed query, time taken etc benchmark at the bottom of the page.

Read The Complete CodeIgniter Tutorials Series By CodeSamplez.com

Drawbacks:

Actually, if we talk about finding a way to create SQL queries easier, then its do the work. But, for better development or if we want to follow another technology like ORM(Object-relational mapper), then it won’t fulfill our needs. Moreover, it also doesn’t provide support for long-range database engines(create different SQL syntax for a different database like MySQL, SQL server, oracle etc). In this case, we will just need to get help from third-party tools like doctrine, which are easily integrable with Codeigniter as a plugin/library.

Also, another drawback(or should I say lacking enhancement?), the active record class doesn’t provide any function to perform “UNION” operation directly, at least, I didn’t find any(Correct me if I am wrong).

Perform Union operation using active record class:

Although most of the functions are provided as alternatives to SQL syntax, like select() for ‘SELECT’, where() for ‘WHERE’, there was no implemented method for UNION operation. So, if you are going to need to execute a query that requires ‘UNION’ , you may be thinking about what to do, avoid using an active record class. If not, and its possible to use it there then how?

If you are thinking for performance, best is to avoid active record and write raw query. But, if you are not comfortable in writing raw query and looking for a codeigniter active record based alternative solution, there is one for you as well.

Although it’s not possible to use ‘UNION’ directly, still, we can implement it with the use of an active record class. Here is how:
Let’s assume we are merging two different columns into one in the same database table(One of the possible reasons why we need to use UNION). The code should be something like this:

function get_merged_result($ids){
		$this->db->select("column");
		$this->db->distinct();
		$this->db->from("table_name");
		$this->db->where_in("id",$model_ids);
		$this->db->get();
		$query1 = $this->db->last_query();

		$this->db->select("column2 as column");
		$this->db->distinct();
		$this->db->from("table_name");
		$this->db->where_in("id",$model_ids);

		$this->db->get();
		$query2 =  $this->db->last_query();
		$query = $this->db->query($query1." UNION ".$query2);

		return $query->result();
	}
Code language: PHP (php)

Here, what we are doing is simply getting help from the CodeIgniter active record class to prepare two different queries and then we are manually constructing our original query. That’s all. The above function will return the correct results with desired UNION operation.

Let me know(by commenting) if you found something more which aren’t implemented properly or if you have any other question related to this so that I can start finding a solution to them and update this post by adding them and solution PHP code samples also. Happy coding 🙂