Skip to content
captainkuro edited this page Jan 11, 2013 · 45 revisions

Category:Library::Database

This page details an implementation of Active Record for CodeIgniter that more accurately mimics the way Ruby on Rails works with models. It allows you to abstract large amounts of the querying and creation code out of your models, and can help with streamlining your controller code as well.

File:Activerecord.0.3.2.php.zip

NOTE! Due to some of the code this technique uses, it will only work if you are running PHP5! But, check the forum thread for the changes needed to make a partly-working PHP4 version.

In order to avoid useless notices when saving or updating, patch the save() and update() method like this:

foreach ($this->_columns as $column)
        {
            if ($column->Field != 'id' && property_exists($this,$column->Field)) 
                        {
                            $data[$column->Field] = $this->{$column->Field};
                        }
        }

Implementation

To begin using the ActiveRecord class, download the most recent version and save it into your /application/libraries folder. This file replaces the CodeIgniter Model class, and all your models will inherit from it.

Next, edit /application/config/autoload.php and make sure that both the ActiveRecord and database libraries are automatically loaded when CodeIgniter runs:

$autoload['libraries'] = array('activerecord', 'database');

You are now ready to start using the ActiveRecord class in your application.

Convention Over Configuration

Before we get onto actually using the class, let's talk a little about the approach Ruby on Rails takes to models and database design, and in particular its principle of "Convention over configuration".

When creating your database tables, there are certain conventions that the ActiveRecord class assumes you to be following. These are:

  • Every model maps to a database table
  • The table name will be the lowercase pluralised model name
  • Every table's unique key will be named id
  • Relationships between tables will be handled by a separate relationship table, named for the two tables it connects, in alphabetical order, separated by an underscore ("_")

Some examples to clarify those three requirements:

  • I have a model named Page, so the table it maps to is named pages
  • I have a model named Person, so the table it maps to is named people
  • The pages table contains fields: -- id -- title -- content
  • The people table contains fields: -- id -- first_name -- last_name
  • Relationships between people and pages are held in a table named pages_people
  • The pages_people table contains fields: -- page_id -- person_id

Hopefully that all makes sense and seems fairly straightforward.

Creating A Model

Continuing the example above, let's create our Page model:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

class Page extends ActiveRecord {

    function __construct()
    {
        parent::ActiveRecord();
        $this->_class_name = strtolower(get_class($this));
        $this->_table = $this->_class_name . 's';
        $this->_columns = $this->discover_table_columns();
    }

}

?>

This is the basic template for all models using ActiveRecord - and, in many cases, that is all you will need to write in the model file! When the object is instantiated, it stores some meta information about itself: its class name, table name, and the columns that exist in its table.

Models for non-standard plurals are only slightly different - here's our Person model:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

class Person extends ActiveRecord {

    function __construct()
    {
        parent::ActiveRecord();
        $this->_class_name = strtolower(get_class($this));
        $this->_table = 'people';
        $this->_columns = $this->discover_table_columns();
    }

}

?>

The only difference is that we need to hardcode the table name instead of extrapolating it from the class name.

Using an ActiveRecord model in your Controller

Now that we have created models to represent our pages and people, let's use them in a controller. Our application will be a simple wiki, where there are multiple pages which have been created by multiple people. Firstly, let's setup our basic controller template:

<?php

class Wiki extends Controller {

    function __construct()
    {
        parent::Controller();
        $Page =& $this->load->model('Page');
        $Person =& $this->load->model('Person');
    }
    
    function index()
    {
        echo "Welcome to my wiki!";
    }
}
?>

Fairly straightforward so far, but note that we have created instances of our two models as global variables inside our controller. They will now be available within our functions.

The most basic functionality we need is to be able to view a page - we'll assume that the URL structure we want is /wiki/view/123, where "123" is the id of the page we want to see:

function view($id)
{
    $data['page'] = $this->Page->find($id);
    $this->load->view('single_page', $data);
}

Pretty cool, right? We didn't have to write anything special in our Page model to be able to use a neat find() method. But suddenly we realise that our URL structure isn't that great - most wikis use the page name, not the database ID! Not a problem, we just need to make a simple change:

function view($title)
{
    $data['page'] = $this->Page->find_by_title($title);
    $this->load->view('single_page', $data);
}

That's right - you can use find_by_fieldname() to query your data using any field name you want! (Actually there are probably encoding issues, or at the very least you'd want to prevent the use of spaces or special characters in the title field, but the principle is sound.)

With both of the above view() functions, the View file you load would look something like this:

<h1><?= $page->title ?></h1>

<?= auto_typography($page->content) ?>

Retrieving and displaying Relationships

Let's modify the above example to include the name of the author(s) of the page. Remembering what we said earlier about relationships between tables, we know that there is a table named pages_people that contains the relationships between all of the pages and people in the database. But how do we write a complicated MySQL JOIN query to find the right data? With ActiveRecord, we don't need to:

function view($title)
{
    $data['page'] = $this->Page->find_by_title($title);
    $data['page']->fetch_related_people('person');
    $this->load->view('single_page', $data);
}

And now in our view file:

<h1><?= $page->title ?></h1>

<?= auto_typography($page->content) ?>

<p>Authors: <?php
foreach ($page->people as $person):
    echo $person->first_name . ' ' . $person->last_name . ', ';
endforeach;
?></p>

With one additional line, we have retrieved an array of Person objects representing all the people linked to the current page. (NB: The fetch_related_objecttype() method only needs an argument if the singular of the object cannot be divined from the plural.)

Other ways to find with ActiveRecord

So we can use find() and pass in an id, or use find_by_fieldname() and pass in anything we want - what other ways are there to get a set of objects?

$all_pages = $this->Page->find(ALL);

By using the ALL constant, you can retrieve an array of all the records in the table - useful for an index listing.

$people_called_john = $this->Person->find_all_by_first_name('John');

Where find_by_fieldname() will only return a single record, you can use find_all_by_fieldname() to get an array of all the matching records.

What about if you want to apply a more complicated query?

$data = array(
    'age >' => 30,
    'country' => 'United Kingdom'
);
$results = $this->Person->find_all_by_first_name('John', $data);

By passing in an array of additional query modifiers (WHERE clauses), we can be more precise with our result set - here we will get back an array of all records for people named John, over the age of 30, living in the UK.

$pages = $this->Page->find_and_limit_by(10, 10);

The find_and_limit_by() function is what you need when you're working with paging result sets. If you are paging through a subset of records, you'll need to pass in the query as well:

$data = array(
    'title' => 'Untitled document'
);
$untitled_pages = $this->Page->find_and_limit_by(10, 10, array(
    $data
));

What else does ActiveRecord do?

To add error handling, you can check whether an object exists() or not:

function view($name)
{
    $data['page'] = $this->Page->find_by_name($name);
    if ($data['page']->exists())
    {
        $this->load->view('single_page', $data);
    }
    {
        show_404();
    }
}

I want to do something different - how do I create new functionality?

If you want to add a new method to your model, you do it in exactly the same way as you normally would in CodeIgniter - create a new function in the model. Just remember that the $this variable refers to an instance of the model class.

For example, let's add a shortcut for retrieving the full name of a person to our Person model:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

class Person extends ActiveRecord {

    function __construct()
    {
        parent::ActiveRecord();
        $this->_class_name = strtolower(get_class($this));
        $this->_table = 'people';
        $this->_columns = $this->discover_table_columns();
    }

    function fullname()
    {
        return $this->first_name . ' ' . $this->last_name;
    }

}

?>

It's that simple! Now there is a $me->fullname(); method that can be used in controllers or views. Add useful extensions to your model and keep data-related manipulation out of your controllers where appropriate.

Administrative functions - Create, Update, and Delete

So we can view our pages and people in various different ways; what about creating or deleting records?

There are two ways to create a new record - the first is to instantiate the object, set various properties, and then call the save() method:

$me = new Person();
$me->first_name = 'Butch';
$me->last_name = 'Cassidy';
$me->save();

The second way to create a new record is to pass an array of data to the create() method:

$data = array(
    'first_name' => 'Sundance',
    'last_name' => 'Kid'
);
$this->Person->create($data);

To update a record, call the update() method (obviously it will only make any difference if you have changed some of the properties of the object before updating):

$me = $this->Person->find_by_first_name('John');
$me->first_name = 'James';
$me->update();

To delete a record, call the delete() method:

$me->delete();

There is also a delete_all() method, in case you want to empty an entire table:

$this->Person->delete_all();

Finally, you may be wondering how to create a relationship between different objects. Handily there is a create_relationship() method that lets you pass one object to another and creates an entry in the corresponding table:

$pages = $this->Page->find(ALL);
$me = $this->Person->find_by_first_name('Buddy');
foreach ($pages as $page)
{
    $me->create_relationship($page);
}
echo "Hooray! I wrote everything!";

Keep Active Record from escaping MySQL functions

By default, Active Record escapes your select statements in order to protect against malicious attacks, like SQL injections. However, the select method has a second optional boolean parameter that you can set to FALSE to keep Active Record from escaping the statement:

$this->db->select("CONCAT(somevalue, anothervalue) AS value", FALSE);

Summary

Most functions accept a few other arguments too - the class is fully commented, so have a look at the code to see what else ActiveRecord can do.

Category:Contributions::Libraries::Database

Clone this wiki locally