A good friend of mine recently asked me how he would create a subquery using Code Igniter's Active Record class. This does present a challenge as Code Igniter's Active Record class does not natively support subqueries.
However, if you look into the code for the Database class you'll see that CI uses some handy utility methods to compile the SQL for processing. These methods exist in all Code Igniter drivers (providing the cross platform abstraction that makes Active Record so valuable in the first place).
This algorithm is definitely far from perfect, but it DOES provide some level of abstraction that manually typing your subquery would not. If you have any questions, concerns, or suggestions feel free to leave them as comments.
// Generate the subquery
$this->db->select('count(*)');
$this->db->from('users');
// Render the subquery to a string
$subQuery = $this->db->_compile_select();
// Reset active record
$this->db->_reset_select();
// Generate the primary query and include the subquery
$this->db->select('users.id as userId, users.fullname as userName');
$this->db->select("($subQuery) as userCount");
$this->db->where('users.status', 'active');
To break this down into smaller parts:
// Generate the subquery
$this->db->select('count(*)');
$this->db->from('users');
This code generates the query: select count(*) from users
// Render the subquery to a string
$subQuery = $this->db->_compile_select();
This code uses Code Igniter's Active Record class to generate an abstracted select statement. Whether we're using Oracle, MySQL, or PostgreSQL the statement will be rendered appropriately by Active Record.
// Reset active record
$this->db->_reset_select();
This method is usually called automatically by the Active Record class after a select statement is completed. This will clear the Active Record cache so that our "select"(count(*)) and "from"(users) commands will not appear in the NEXT active record statement that we create.
// Generate the primary query and include the subquery
$this->db->select('users.id as userId, users.fullname as userName');
$this->db->select("($subQuery) as userCount");
$this->db->where('users.status', 'active');
This code generates a select statement calling back the userID and the userName as well as aliasing the subquery variable string as 'userCount'. Using the MySQL driver the SQL that is output is as follows:
SELECT `users`.`id` as userId, `users`.`fullname` as userName, (SELECT count(*) FROM (`users`)) as userCount FROM (`users`) WHERE `users`.`status` = 'active'
Keep in mind that the developers of Code Igniter did not intend for you to use the _compile_select method. Therefore, it's possible that they may change the functionality in future versions and not bother letting anyone know.
This screencast continues a series with the goal of documenting the development of a functionally complete site using PHP with the CodeIgniter framework and jQuery for Javascript UI improvements including AJAX interactions.
This video covers the creation of a database table for storing users and the CodeIgniter model class for interacting with it.
Errata:
I've noticed a few errors in the screencast. When errors creep up (and they will) I'll audit them as they're discovered and keep this post up to date. Errors will also be updated during the following screencast.
This screencast begins a series with the goal of documenting the development of a functionally complete site using PHP with the Code Igniter framework and jQuery for Javascript UI improvements including AJAX interactions.
This video briefly covers the establishment of a local development environment and the configuration of CodeIgniter.
In this screencast I attempt to explain the structure and terminology of the Code Igniter MVC PHP development framework.
Developing using an MVC framework can go a long way towards increasing the reusability and robustness of your code. In this article I focus on writing model methods for the Code Igniter PHP MVC framework. The concepts themselves are not specific to Code Igniter, but some of the code will be.
CRUD is an acronym that stands for Create, Retrieve, Update, and Delete. These are the most basic types of interactions that your code will have with a data source.
A few examples: Create - adding a user account to your website Retrieve - getting a list of available products Update - changing a user's password or the name of a product Delete - removing a user account or product
When writing models you'll generally start by creating CRUD methods for each of your logical types of data. If you're writing user authentication (login, logout, forgotten password, account activation) you'll want a method to add, update, and delete users along with the ability to pull back the user account information for a single user or for a group of users.
For example:
function AddUser()
function UpdateUser()
function DeleteUser()
function GetUsers()
Most of the interactions that we have with user accounts can be handled by these four methods. Instead of creating a model method "GetActiveUsers" we can just create a parameter for GetUsers that determines the user status of the returned set, more on this below.
Typically parameters for methods are sent in the following manner:
function AddUser($insertData)
function UpdateUser($userId, $updateData)
function DeleteUser($userId)
function GetUsers($limit, $offset)
Naturally, (especially for GetUsers) we'll find more and more parameters to add to our methods. This not only gets very difficult to read, but can create issues where we have to go back and alter the code that we've already written if we have to tack a new parameter onto the method.
For Example:
function GetUsers($limit, $offset, $status)
We would also need a singular GetUser($userId) method in addition to the GetUsers method. This is a problem because we're creating multiple versions of the same method. If we change the way we want information to be returned then we'd need to change code in two places, the GetUser AND GetUsers methods.
To overcome these issues I propose the usage of an options array, as such:
function AddUser($options = array())
function UpdateUser($options = array())
function DeleteUser($options = array())
function GetUsers($options = array())
If, in the past, we'd use GetUsers(5, 5, 'active') to return a list of users, we would now use GetUsers(array('limit' => 5, 'offset' => 5, 'status' => 'active');
Parameters passed using the options array can be sent in any order and can even be completely omitted. As you need to add functional parameters you simply change the model method and will not have to update code anywhere else.
In order to create properly robust methods we're going to need to implement a few common bits of functionality. Namely, the ability to assign required fields and field defaults. For example, a required field when adding a user might be userEmail. For this we create the 'required' method.
/**
* _required method returns false if the $data array does not contain all of the keys assigned by the $required array.
*
* @param array $required
* @param array $data
* @return bool
*/
function _required($required, $data)
{
foreach($required as $field) if(!isset($data[$field])) return false;
return true;
}
In the 'AddUser' example our code might look like this:
/**
* AddUser method creates a record in the users table.
*
* Option: Values
* --------------
* userEmail (required)
* userPassword
* userName
* userStatus active(default), inactive, deleted
*
* @param array $options
*/
function AddUser($options = array())
{
// required values
if(!$this->_required(array('userEmail'), $options)) return false;
// At this point we know that the key 'userEmail' exists in the $options array.
}
Now our AddUser method will bail out and return false if the 'userEmail' key was not sent in the options array. Robust!
Notice that in the PHPdoc block we mention that the default status for newly created users is 'active'. Naturally, if we wanted to create an inactive user we would be able to pass the 'userStatus' parameter with the value inactive. However, we are lazy and prefer to not have to explicitly declare 'active'.
Introducing the 'default' method:
/**
* _default method combines the options array with a set of defaults giving the values in the options array priority.
*
* @param array $defaults
* @param array $options
* @return array
*/
function _default($defaults, $options)
{
return array_merge($defaults, $options);
}
That's it, this method consists of a single command. I decided to create a method out of this because I may want to add extra functionality to the defaults method, and don't want to have to change every single one of my model methods.
Let's go ahead and implement the 'default' method:
/**
* AddUser method creates a record in the users table.
*
* Option: Values
* --------------
* userEmail (required)
* userPassword
* userName
* userStatus active(default), inactive, deleted
*
* @param array $options
*/
function AddUser($options = array())
{
// required values
if(!$this->_required(array('userEmail'), $options)) return false;
// default values
$options = $this->_default(array('userStatus' => 'active'), $options);
}
// At this point we know that the 'userEmail' key exists in the $options array and if no 'userStatus' key existed before, it does now and with the value 'active'.
These methods go a long way towards making your code more robust while only adding a few lines to each method.
Many database constructs (MySQL, Oracle, Microsoft SQL, PostgreSQL, etc) use variations of SQL syntax, but they all work a little bit differently. An active record class allows us to create a database query using abstraction. In other words we are able to create queries that will work on any database construct that our class supports. They also give the added benefit of allowing us to send bits of the query to the class one by one before executing the query.
A Code Igniter active record query might look something like this:
$this->db->where('userStatus', 'active');
$this->db->get('users');
Those two commands will create and execute the query, "select * from users where userStatus = 'active'"
Using the concepts that we've explored let's look at a simple example set CRUD methods.
/**
* AddUser method creates a record in the users table.
*
* Option: Values
* --------------
* userEmail (required)
* userPassword
* userName
* userStatus active(default), inactive, deleted
*
* @param array $options
*/
function AddUser($options = array())
{
// required values
if(!$this->_required(array('userEmail'), $options)) return false;
// default values
$options = $this->_default(array('userStatus' => 'active'), $options);
// qualification (make sure that we're not allowing the site to insert data that it shouldn't)
$qualificationArray = array('userEmail', 'userName', 'userStatus');
foreach($qualificationArray as $qualifier)
{
if(isset($options[$qualifier])) $this->db->set($qualifier, $options[$qualifier]);
}
// MD5 the password if it is set
if(isset($options['userPassword'])) $this->db->set('userPassword', md5($options['userPassword']));
// Execute the query
$this->db->insert('users');
// Return the ID of the inserted row, or false if the row could not be inserted
return $this->db->insert_id();
}
/**
* UpdateUser method alters a record in the users table.
*
* Option: Values
* --------------
* userId the ID of the user record that will be updated
* userEmail
* userPassword
* userName
* userStatus active(default), inactive, deleted
*
* @param array $options
* @return int affected_rows()
*/
function UpdateUser($options = array())
{
// required values
if(!$this->_required(array('userId'), $options)) return false;
// qualification (make sure that we're not allowing the site to update data that it shouldn't)
$qualificationArray = array('userEmail', 'userName', 'userStatus');
foreach($qualificationArray as $qualifier)
{
if(isset($options[$qualifier])) $this->db->set($qualifier, $options[$qualifier]);
}
$this->db->where('userId', $options['userId']);
// MD5 the password if it is set
if(isset($options['userPassword'])) $this->db->set('userPassword', md5($options['userPassword']));
// Execute the query
$this->db->update('users');
// Return the number of rows updated, or false if the row could not be inserted
return $this->db->affected_rows();
}
/**
* GetUsers method returns an array of qualified user record objects
*
* Option: Values
* --------------
* userId
* userEmail
* userStatus
* limit limits the number of returned records
* offset how many records to bypass before returning a record (limit required)
* sortBy determines which column the sort takes place
* sortDirection (asc, desc) sort ascending or descending (sortBy required)
*
* Returns (array of objects)
* --------------------------
* userId
* userEmail
* userName
* userStatus
*
* @param array $options
* @return array result()
*/
function GetUsers($options = array())
{
// default values
$options = $this->_default(array('sortDirection' => 'asc'), $options);
// Add where clauses to query
$qualificationArray = array('userId', 'userEmail', 'userStatus');
foreach($qualificationArray as $qualifier)
{
if(isset($options[$qualifier])) $this->db->where($qualifier, $options[$qualifier]);
}
// If limit / offset are declared (usually for pagination) then we need to take them into account
if(isset($options['limit']) && isset($options['offset'])) $this->db->limit($options['limit'], $options['offset']);
else if(isset($options['limit'])) $this->db->limit($options['limit']);
// sort
if(isset($options['sortBy'])) $this->db->order_by($options['sortBy'], $options['sortDirection']);
$query = $this->db->get('users');
if($query->num_rows() == 0) return false;
if(isset($options['userId']) && isset($options['userEmail']))
{
// If we know that we're returning a singular record, then let's just return the object
return $query->row(0);
}
else
{
// If we could be returning any number of records then we'll need to do so as an array of objects
return $query->result();
}
}
/**
* DeleteUser method removes a record from the users table
*
* @param array $options
*/
function DeleteUser($options = array())
{
// required values
if(!$this->_required(array('userId'), $options)) return false;
$this->db->where('userId', $options['userId']);
$this->db->delete('users');
}
Here are some examples of how we can use these methods to interact with your database.
Adding a User
$userId = $this->user_model->AddUser($_POST);
if($userId)
echo "The user you have created has been added successfully with ID #" . $userId;
else
echo "There was an error adding your user.";
Updating a User
if($this->user_model->UpdateUser(array('userId' => 3, 'userName' => 'Shawn', 'userEmail' => 'not telling')))
// The user has been successfully updated
else
// The user was not updated
User Authentication (Retrieving a Single User)
$user = $this->user_model->GetUsers(array('userEmail' => $userEmail, 'userPassword' => md5($userPassword), 'userStatus' => 'active'));
if($user)
// Log the user in
else
// Sorry, your user / password combination isn't correct.
Retrieving a Set of Users
$users = $this->user_model->GetUsers(array('userStatus' => 'active'));
if($users)
{
echo "Active Users<br />";
foreach($users as $user)
{
echo $user->userName . "<br />";
}
}
else
{
echo "There are no active users.";
}
Deleting a User
$this->user_model->DeleteUser(array('userId' => $userId));
I hope that this will continue a dialog on how to better write such commonly reused bits of functionality. If you have any suggestions or comments please let me know. I'd love to hear about the algorithms that you've been designing to handle similar problems.
William Rufino has translated this article to Portuguese. This translation can be found here: http://www.williamrufino.com.br/blog/como-escrever-um-model-melhor-no-codeigniter
In this screencast we explore the jQuery $.post() command in order to create a simple AJAX login script that also gracefully degrades and works for users who do not have JavaScript enabled.
You may want to check out my post "What Exactly is JSON?" if you're not familiar with the JavaScript Object Notation before watching.