DataMapper


Get

You'll find Get is used in a very similar way to retrieving data with CodeIgniter's Active Record class. All the relevant query clauses from Active Record are available in DataMapper so you have the full power of retrieving data, in Active Record style!

Note: There are enough differences between CodeIgniter and DataMapper's Active Record like query clauses that you should read on to be able to take full advantage of it.


Now, let's look at all the available methods. We'll assume we have a DataMapper model setup, named Object.

$object->get();

Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:

$o = new Object();
$o->get();

// The $o object is populated with all objects from its corresponding table

The first and second parameters enable you do set a limit and offset clause:

$o = new Object();
$o->get(10, 20);

// The $o object is populated with 10 objects from its corresponding table, starting from record 20

You can view the results in a couple of ways. Viewing the first result:

$o = new Object();
$o->get();

echo $o->title;

Viewing all results:

$o = new Object();
$o->get();

foreach ($o->all as $obj)
{
    echo $obj->title;
}

$object->validate->get();

Normally, get() will generate its query from building up any query clauses you have setup before calling get(). If none are setup, it will default to selecting all records from the objects corresponding table. However, there is a special situation where get() will use the values present within the current object. This happens if you run the validate() function before a get() call.

Note: When doing $object->validate()->get(); all other query clauses (such as select, where etc) will be ignored.

Ok, so to explain this, I'll use an example I've used before from the Getting Started page. Taking part of the example from there, we see that the User model is setup to encrypt the password field with the salt from the matching users stored record (by username), when they attempt to login.

User model (excerpt)

function login()
{
    // Create a temporary user object
    $u = new User();

    // Get this users stored record via their username
    $u->where('username', $this->username)->get();

    // Give this user their stored salt
    $this->salt = $u->salt;

    // Validate and get this user by their property values,
    // this will see the 'encrypt' validation run, encrypting the password with the salt
    $this->validate()->get();

    // If the username and encrypted password matched a record in the database,
    // this user object would be fully populated, complete with their ID.

    // If there was no matching record, this user would be completely cleared so their id would be empty.
    if (empty($this->id))
    {
        // Login failed, so set a custom error message
        $this->error_message('login', 'Username or password invalid');

        return FALSE;
    }
    else
    {
        // Login succeeded
        return TRUE;
    }
}

Here's how the models login function was called. You can see the username and unencrypted password is set on the user object before calling the login function.

Controller (excerpt)

// Create user object
$u = new User();

// Put user supplied data into user object
// (no need to validate the post variables in the controller,
// if you've set your DataMapper models up with validation rules)
$u->username = $this->input->post('username');
$u->password = $this->input->post('password');

// Attempt to log user in with the data they supplied, using the login function setup in the User model
// You might want to have a quick look at that login function up the top of this page to see how it authenticates the user
if ($u->login())
{
    echo '<p>Welcome ' . $this->username . '!</p>';
    echo '<p>You have successfully logged in so now we know that your email is ' . $this->email . '.</p>';
}
else
{
    // Show the custom login error message
    echo '<p>' . $this->error->login . '</p>';
}

So, inside, the models login function, $object->validate->get(); is called which runs the validation functions, defined in the model, on the objects properties, and then it does a get using the validated properties.

$object->get_where();

Identical to the above function except that it permits you to add a "where" clause in the first parameter, instead of using the $object->where() function:

$o = new Object();
$o->get_where(array('id', => $id), $limit, $offset);

Please read the where function below for more information.

$object->select();

Permits you to write the SELECT portion of your query:

$o = new Object();
$o->select('title, description');

$o->get();

// The $o object is populated with all objects from its corresponding table, but with only the title and description fields populated

Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, DataMapper assumes you wish to SELECT *

$object->select_max();

Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field.

$o = new Object();
$o->select_max('age');
$o->get();

// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the maximum age

$object->select_min();

Writes a "SELECT MIN(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$o = new Object();
$o->select_min('age');
$o->get();

// The $o object is populated with a signle object from its corresponding table, but with only the age field populated, which contains the minimum age

$object->select_avg();

Writes a "SELECT AVG(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$o = new Object();
$o->select_avg('age');
$o->get();

// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the average age

$object->select_sum();

Writes a "SELECT SUM(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

$o = new Object();
$o->select_sum('age');
$o->get();

// The $o object is populated with a single object from its corresponding table, but with only the age field populated, which contains the sum of all ages

$object->where();

This function enables you to set WHERE clauses using one of four methods:

Note: All values passed to this function are escaped automatically, producing safer queries.

  1. Simple key/value method: $o = new Object();
    $o->where('name', $name);

    // When $o->get() is called, the above where clause will be included in the get query

    If you use multiple where function calls they will be chained together with AND between them:

    $o = new Object();
    $o->where('name', $name);
    $o->where('title', $title);
    $o->where('status', $status);

    // When $o->get() is called, all of the above where clause will be included in the get query
  2. Custom key/value method:

    You can include an operator in the first parameter in order to control the comparison:

    $o = new Object();
    $o->where('name !=', $name);
    $o->where('id <', $id);

    // When $o->get() is called, all of the above where clause will be included in the get query (with operators)
  3. Associative array method: $o = new Object();
    $array = array('name' => $name, 'title' => $title, 'status' => $status);

    $o->where($array);

    // When $o->get() is called, the array of where clauses will be included in the get query

    You can include your own operators using this method as well:

    $array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);

    $o = new Object();
    $o>where($array);
  4. Custom string:

    You can write your own clauses manually:

    $where = "name='Joe' AND status='boss' OR status='active'";

    $o = new Object();
    $o->where($where);

$object->or_where();

This function is identical to the one above, except that multiple instances are joined by OR:

$o = new Object();
$o->where('name !=', $name);
$o->or_where('id >', $id);

// When $o->get() is called, all of the above where clause will be included in the get query separated by OR's

$object->where_in();

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

$o = new Object();
$names = array('Frank', 'Todd', 'James');
$o->where_in('username', $names);

// When $o->get() is called, all records where the username is Frank, Todd, or James will be returned

$object->or_where_in();

Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate

$o = new Object();
$firstnames = array('Frank', 'Todd', 'James');
$lastnames = array('Smith', 'Jones');
$o->where_in('firstname', $firstnames);
$o->or_where_in('lastname', $lastnames);
// When $o->get() is called, all records where the firstname is Frank, Todd, or James, or all records where the lastname is Smith or Jones, will be returned

$o->where_not_in();

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate

$o = new Object();
$names = array('Frank', 'Todd', 'James');
$o->where_not_in('username', $names);
// When $o->get() is called, all records where the username is not Frank, Todd, or James will be returned

$object->or_where_not_in();

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate

$o = new Object();
$firstnames = array('Frank', 'Todd', 'James');
$lastnames = array('Smith', 'Jones');
$o->where_not_in('firstname', $firstnames);
$o->or_where_not_in('lastname', $lastnames);
// When $o->get() is called, all records where the firstname is not Frank, Todd, or James, or all records where the lastname is not Smith or Jones, will be returned

$object->like();

This function enables you to generate LIKE clauses, useful for doing searches.

Note: All values passed to this function are escaped automatically.

  1. Simple key/value method: $o = new Object();
    $o->like('title', 'match');

    // When $o->get() is called, all records with a title like match will be returned

    If you use multiple function calls they will be chained together with AND between them:

    $o = new Object();
    $o->like('title', 'match'); $o->like('body', 'match');

    // When $o->get() is called, all records with a title like match and a body like match will be returned
    If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default). $o = new Object();
    $o->like('title', 'match', 'after');

    // When $o->get() is called, all records with a title starting with match will be returned
  2. Associative array method: $array = array('title' => $match, 'page1' => $match, 'page2' => $match);

    $o = new Object();
    $o->like($array);

    // When $o->get() is called, all records with the title, page1, and page2 like the specified matches will be returned

$object->or_like();

This function is identical to the one above, except that multiple instances are joined by OR:

$o = new Object();
$o->like('title', 'match');
$o->or_like('body', $match);

// When $o->get() is called, all records with a title like match or a body like match will be returned

$object->not_like();

This function is identical to like(), except that it generates NOT LIKE statements:

$o = new Object();
$o->not_like('title', 'match');

// When $o->get() is called, all records with a title not like match will be returned

$object->or_not_like();

This function is identical to not_like(), except that multiple instances are joined by OR:

$o = new Object();
$o->like('title', 'match'); $o->or_not_like('body', 'match');

// When $o->get() is called, all records with a title like match or a body not like match will be returned

$object->group_by();

Permits you to write the GROUP BY portion of your query:

$o = new Object();
$o->group_by('title');

// When $o->get() is called, all returned records will be grouped by title

You can also pass an array of multiple values as well:

$o = new Object();
$o->group_by(array('title', 'date'));

// When $o->get() is called, all returned records will be grouped by title and then date

$object->distinct();

Adds the "DISTINCT" keyword to a query

$o = new Object();
$o->distinct();

// When $o->get() is called, a DISTINCT select of records will be made

$object->having();

Permits you to write the HAVING portion of your query. There are 2 possible syntaxe, 1 argument or 2:

$o = new Object();
$o->having('user_id = 45');
// When $o->get() is called, all records having a user_id of 45 will be returned

$o->having('user_id', 45);
// As above, when $o->get() is called, all records having a user_id of 45 will be returned

You can also pass an array of multiple values as well:

$o = new Object();
$o->having(array('title =' => 'My Title', 'id <' => $id));

// When $o->get() is called, all records having a title of My Title and an id less than 45 will be returned

If you are using a database that CodeIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to FALSE.

$o = new Object();
$o->having('user_id', 45, FALSE);

$object->or_having();

Identical to having(), only separates multiple clauses with "OR".

$object->order_by();

Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc, or random.

$o = new Object();
$o->order_by("title", "desc");

// When $o->get() is called, all returned records will be ordered by title descending

You can also pass your own string in the first parameter:

$o = new Object();
$o->order_by('title desc, name asc');

// When $o->get() is called, all returned records will be ordered by title descending, then name ascending

Or multiple function calls can be made if you need multiple fields.

$o = new Object();
$o->order_by("title", "desc");
$o->order_by("name", "asc");

// When $o->get() is called, all returned records will be ordered by title descending, then name ascending

Note: random ordering is not currently supported in Oracle or MSSQL drivers. These will default to 'ASC'.

$object->limit();

Lets you limit the number of rows you would like returned by the query:

$o = new Object();
$o->limit(10);

// When $o->get() is called, the number of records returned will be limited to 10

The second parameter lets you set a result offset.

$o = new Object();
$o->limit(10, 20);

// When $o->get() is called, the number of records returned will be limited to 10, starting from record 20

 

 Method Chaining

Method chaining allows you to simplify your syntax by connecting multiple functions. Consider this example:

$o = new Object();
$o->where('id', $id)->limit(10, 20)->get();

Note: Method chaining only works with PHP 5. Funnily enough, DataMapper also only works with PHP 5 so if it works on your server, so will method chaining!

The alternate of the above without method chaining would be:

$o = new Object();
$o->where('id', $id);
$o->limit(10, 20);
$o->get();

 

 Active Record Caching

Since DataMapper uses Active Record for all its queries, it makes sense you should be able to access the Active Record caching methods. While not "true" caching, Active Record enables you to save (or "cache") certain parts of your queries for reuse later. Normally, when an Active Record call is completed, all stored information is reset for the next call. With caching, you can prevent this reset, and reuse information easily.

Cached calls are cumulative. If you make 2 cached select() calls, and then 2 uncached select() calls, this will result in 4 select() calls. There are three Caching functions available:

$object->start_cache()

This function must be called to begin caching. All Active Record queries of the correct type (see below for supported queries) are stored for later use.

$object->stop_cache()

This function can be called to stop caching.

$object->flush_cache()

This function deletes all items from the Active Record cache.

Here's a usage example:

$o = new Object();
$o->start_cache();
$o->select('field1');
$o->stop_cache();
$o->get();

// The $o object is populated with all records from its corresponding table, but with only the 'field1' field being populated

$o->select('field2');
$o->get();

// The $o object is populated with all records from its corresponding table, but with both the 'field1' and 'field2' fields being populated

$o->flush_cache();

$o->select('field2');
$o->get();

// The $o object is populated with all records from its corresponding table, but with only the 'field2' field being populated

Note: The following fields can be cached: ‘select’, ‘from’, ‘join’, ‘where’, ‘like’, ‘group_by’, ‘having’, ‘order_by’, ‘set’

 

 Get (Advanced)

DataMapper has extended versions of most of its query clauses that allow for advanced querying on relationships.

Let's go through an example to see the benefits. Let's say we have a User model and a Group model. A group can have many users but a user can only have one group. Here's how you would look up all users belonging to the Moderator group without the advanced query:

// Create user object
$u = new User();

// Get all users
$u->get();

// Loop through all users
foreach ($u->all as $user)
{
    // Get the current user's group
    $user->group->get();
    
    // Check if user is related to the Moderator group
    if ($user->group->name == 'Moderator')
    {
        // ...
    }
}

Here's how you would do the above, but using an advanced query:

// Create user object
$u = new User();

// Get users that are related to the Moderator group
$u->where_related_group('name', 'Moderator')->get();

// ...

As you can see, it's a big time saver but not just in the amount of code you write, but also in the number of database queries and overall processing time.

Supported Query Clauses

The following are the normal query clauses that you can use in the advanced queries:

$object->{query}_related_{model}($field, $value);

There are a number of ways you can use these advanced queries, and this is the first usage format. All examples are done with the User and Group objects scenario.

Here's an example using the where query:

// Create user
$u = new User();

// Get all users relating to the Moderator group (goes by 'group', 'name', 'Moderator')
$u->where_related_group('name', 'Moderator')->get();

$object->{query}_related($model, $field, $value);

Alternatively, rather than specifying the related model as part of the method, you could instead supply it as the first parameter.

Here's an example using the where query:

// Create user
$u = new User();

// Get all users relating to the Moderator group (goes by 'group', 'name', 'Moderator')
$u->where_related('group', 'name', 'Moderator')->get();

$object->{query}_related($related_object, $field, $value);

Note:  Both the $field and $value parameters are optional if the $related_object contains a valid id.

Here's an example using the where query:

// Create and get the Moderator group
$g = new Group();
$g->get_by_name('Moderator');

// Create user
$u = new User();

// Get all users relating to the Moderator group (goes by 'group', 'id', $g->id)
$u->where_related($g)->get();

Here's a similar way of doing the above, but with an unpopulated related object (no id):

// Create and get the Moderator group
$g = new Group();

// Create user
$u = new User();

// Get all users relating to the Moderator group (goes by 'group', 'name', 'Moderator')
$u->where_related($g, 'name', 'Moderator')->get();

Which of the available usage formats you use will depend on your personal preference, although you should be consistent with your choice. It also might depend on whether you have a related object already available to use.

Note:  For Self Referencing Relationships, you can only use the usage format where you pass a related object with an id, for example:

$object->{query}_related($related_object);