Adding Ordering and Grouping Clauses to the CodeIgniter Library with Method Chaining

Welcome to the tenth installment of a series on method chaining in PHP 5. Comprised of twelve tutorials, this series teaches you the key concepts that surround the implementation of chainable methods within PHP classes, and shows how to put them to work to create a custom library for the popular CodeIgniter framework.

And now that you know what to expect from this series of articles, it’s time to refresh the topics that were discussed in the last one. In that part of the series I explained how to add some chainable methods to the already familiar “AbstractModel” class, which was a pretty useful add-on for CodeIgniter.

Expressed in simple terms, the methods that I implemented within the class were responsible for creating the SELECT MIN, SELECT SUM and SELECT AVG parts of a SELECT SQL statement respectively. However, by sticking to the method chaining approach, it’s also feasible to create methods that build other portions of a query, such as the JOIN, ORDER BY, GROUP BY, LIKE and NOT LIKE modifiers, and a few others as well.

So, with that premise in mind, in this tenth chapter of the series, I’m going to code a few more methods, (also chainable, of course) that will be charged with performing the aforementioned tasks in a simple manner.

Now, let’s leave the preliminaries behind and continue expanding the functionality of the CodeIgniter custom library by means of the method chaining approach. Let’s go!

{mospagebreak title=Creating the JOIN, ORDER BY and GROUP parts of a query}

Before I proceed to add to the custom model library the additional chainable methods that I mentioned in the introduction, it’d be convenient to recall how the library looked previously. So, here is its full source code, as shown in the preceding article:

The MIT License

 

Copyright (c) 2008 Simon Stenhouse

 

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

 

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

 

 

class AbstractModel

{

protected $table = ”;

protected $fields = array();

protected $validation = array();

protected $error_prefix = ‘<p>';

protected static $instance = NULL;

protected $ci = NULL;

protected $db = NULL;

 

// Factory method that creates a singleton model object

public static function factory($model)

{

if (self::$instance == NULL)

{

$model = ucfirst($model);

self::$instance = new $model;

}

return self::$instance;

}

 

// Constructor

public function __construct()

{

$this->ci = & get_instance();

$this->db = $this->ci->db;

$table = strtolower(get_class($this)) . ‘s';

if ($this->db->table_exists($table))

{

$this->table = $table;

$this->fields = $this->db->field_names($this->table);

}

else

{

return;

}

}

 

// Sets a new property for the model

function __set($property, $value)

{

if(in_array($property, array_merge($this->fields, array(‘error’, ‘result’)), TRUE))

{

$this->$property = $value;

}

}

 

// Gets the value of an existing property of the model

function __get($property)

{

if(isset($this->$property))

{

return $this->$property;

}

return NULL;

}

 

// Fetches rows from specified table

public function fetch($limit = NULL, $offset = NULL)

{

$data = array();

foreach ($this->fields as $field)

{

if (isset($this->$field) AND $this->$field != ”)

{

$data[$field] = $this->$field;

}

}

$query = !empty($data) ? $this->db->get_where($this->table, $data, $limit, $offset) : $this->db->get($this->table, $limit, $offset);

if ($query->num_rows() > 0)

{

$this->result = $query->result();

return $this;

}

$this->error = ‘No rows were returned.';

return FALSE;

}

 

// Inserts a new row into the specified database table

public function save()

{

$data = array();

foreach ($this->fields as $field)

{

if (isset($this->$field))

{

$data[$field] = $this->$field;

 

}

}

// if there is any data available go ahead and save/update row

if( !empty($data))

{

// validate input data

if ($this->validate($data) === FALSE)

{

$this->error = $this->get_error_string();

return FALSE;

}

// if id property has been set in the controller update existing row

if ( !empty($this->id))

{

// Update existing record

$this->db->where(‘id’, $this->id);

$this->db->update($this->table, $data);

}

else

{

// otherwise insert new row

$this->db->insert($this->table, $data);

$this->id = $this->db->insert_id();

}

return TRUE;

}

$this->error = ‘No valid data was provided to save row.';

return FALSE;

}

 

// Deletes a row

public function delete()

{

if (isset($this->id))

{

$this->db->where(‘id’, $this->id);

$this->db->delete($this->table);

return TRUE;

}

$this->error = ‘Error deleting row.';

return FALSE;

}

 

// Builds SELECT part of the query

public function select($select = ‘*’, $protect_identifiers = TRUE)

{

if ($select != ‘*’ AND !empty($select))

{

$select = explode(‘,’, $select);

foreach ($select as $key => $field)

{

if ( !in_array($field, $this->fields, TRUE))

{

unset($select[$key]);

}

}

$select = !empty($select) ? $select : ‘*';

}

$this->db->select($select, $protect_identifiers);

return $this;

}

 

// Builds the select MAX part of the query

public function select_max($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_max($field, $alias);

}

return $this;

}

 

// Builds the select MIN part of the query

public function select_min($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

 

// Builds the select AVG part of the query

public function select_average($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

 

// Builds the select SUM part of the query

public function select_sum($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

}

Now that you’ve recalled how the above “AbstractModel” class was defined, it’s time to make it a bit more functional. To do that, I’m going to code other chainable methods. These will be tasked with creating in turn the JOIN, ORDER BY, GROUP BY, LIKE, OR LIKE, NOT LIKE and DISTINCT query modifiers. 

Here are the corresponding definitions for these methods:

// Builds the JOIN part of the query

public function join($table, $join, $join_type = ”)

{

if ( !empty($table) AND !empty($join))

{

$this->db->join($table, $join, $join_type);

}

return $this;

}

 

// Builds the ORDER BY part of the query

public function order_by($field = ‘id’, $order = ‘ASC’)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->order_by($field, $order);

}

return $this;

}

 

// Builds the GROUP BY part of the query

public function group_by($field)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->group_by($field);

}

return $this;

}

 

// Builds the LIKE part of the query using the AND operator

public function like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->like($field, $match, $position);

}

return $this;

}

 

// Builds the OR LIKE part if the query using the OR operator

public function or_like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->or_like($field, $match, $position);

}

return $this;

}

 

// Builds the NOT LIKE part of the query

public function not_like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->not_like($field, $match, $position);

}

return $this;

}

 

// Builds the DISTINCT part of the query

public function distinct()

{

$this->db->distinct();

return $this;

}

Definitely, if you’re familiar with the CodeIgniter database class, then you won’t have major problems grasping the logic that drives the previous chainable methods. They behave as simple proxies for their counterparts in the class.

Of course, all of these methods returns to client code an instance of the custom model class, which makes it really easy to chain them to others. That was pretty simple to understand, wasn’t it?

So far, so good. At this point, the custom model class looks much more functional, since now it’s capable of adding some other modifiers to the SELECT statement. So, what’s the next step? In the next section I’m going to code another set of chainable methods, which will be charged with adding several WHERE clauses to a query.

To see how these methods will be implemented, click on the link below and keep reading.

{mospagebreak title=Conditional queries: adding WHERE modifiers to a SELECt statement}

In reality, it’s pretty easy to add to the previous custom model class a few additional chainable methods that build several WHERE parts of a query. To demonstrate, below I included the definitions of these concrete methods, so take some time to examine them closely.

Here they are:

// Builds the WHERE part of the query using AND and other operators

public function get_where($where, $protect_identifiers = TRUE)

{

if ((is_string($where) OR is_array($where)) AND !empty($where))

{

$this->db->where($where, $protect_identifiers);

}

return $this;

}

 

// Builds the WHERE part of the query using OR and other operators

public function get_or_where($where, $protect_identifiers = TRUE)

{

if ((is_string($where) OR is_array($where)) AND !empty($where))

{

$this->db->or_where($where, $protect_identifiers);

}

return $this;

}

 

// Builds the WHERE IN part of the query

public function where_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->where_in($field, $values);

}

return $this;

}

 

// Builds the WHERE NOT IN part of the query

public function where_not_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->where_not_in($field, $values);

}

return $this;

}

 

// Builds the OR WHERE NOT IN part of the query using the OR operator

public function or_where_not_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->or_where_not_in($field, $values);

}

return $this;

}

Undoubtedly, understanding how the above methods work should be pretty straightforward, since they’ve been implemented similarly to the methods that you learned in the previous segment.

Of course, in this particular case they behave as wrappers for their equivalents in the CodeIgniter database class, which makes it really easy to dynamically add several query modifiers, like WHERE, WHERE NOT IN, OR WHERE, and so forth to a given SQL query.

Assuming that you already grasped the logic that drives the previous chainable methods, it’s time to list the whole source code of the abstract model class, this time including the definitions of the methods in question.

This will be done in the last section of this tutorial. Therefore, go ahead and read the final segment. It’s only one click away.

{mospagebreak title=The custom model class’s full source code}

As I stated in the prior segment, it’s time to see how the set of chainable methods defined earlier fits in into the structure of the abstract model class. How can this be done? Simply by showing its full source code, which has been included below. Take a look at it:

The MIT License

 

Copyright (c) 2008 Simon Stenhouse

 

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

 

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

 

class AbstractModel

{

protected $table = ”;

protected $fields = array();

protected $validation = array();

protected $error_prefix = ‘<p>';

protected static $instance = NULL;

protected $ci = NULL;

protected $db = NULL;

 

// Factory method that creates a singleton model object

public static function factory($model)

{

if (self::$instance == NULL)

{

$model = ucfirst($model);

self::$instance = new $model;

}

return self::$instance;

}

 

// Constructor

public function __construct()

{

$this->ci = & get_instance();

$this->db = $this->ci->db;

$table = strtolower(get_class($this)) . ‘s';

if ($this->db->table_exists($table))

{

$this->table = $table;

$this->fields = $this->db->field_names($this->table);

}

else

{

return;

}

}

 

// Sets a new property for the model

function __set($property, $value)

{

if(in_array($property, array_merge($this->fields, array(‘error’, ‘result’)), TRUE))

{

$this->$property = $value;

}

}

 

// Gets the value of an existing property of the model

function __get($property)

{

if(isset($this->$property))

{

return $this->$property;

}

return NULL;

}

 

// Fetches rows from specified table

public function fetch($limit = NULL, $offset = NULL)

{

$data = array();

foreach ($this->fields as $field)

{

if (isset($this->$field) AND $this->$field != ”)

{

$data[$field] = $this->$field;

}

}

$query = !empty($data) ? $this->db->get_where($this->table, $data, $limit, $offset) : $this->db->get($this->table, $limit, $offset);

if ($query->num_rows() > 0)

{

$this->result = $query->result();

return $this;

}

$this->error = ‘No rows were returned.';

return FALSE;

}

 

// Inserts a new row into the specified database table

public function save()

{

$data = array();

foreach ($this->fields as $field)

{

if (isset($this->$field))

{

$data[$field] = $this->$field;

 

}

}

// if there is any data available go ahead and save/update row

if( !empty($data))

{

// validate input data

if ($this->validate($data) === FALSE)

{

$this->error = $this->get_error_string();

return FALSE;

}

// if id property has been set in the controller update existing row

if ( !empty($this->id))

{

// Update existing record

$this->db->where(‘id’, $this->id);

$this->db->update($this->table, $data);

}

else

{

// otherwise insert new row

$this->db->insert($this->table, $data);

$this->id = $this->db->insert_id();

}

return TRUE;

}

$this->error = ‘No valid data was provided to save row.';

return FALSE;

}

 

// Deletes a row

public function delete()

{

if (isset($this->id))

{

$this->db->where(‘id’, $this->id);

$this->db->delete($this->table);

return TRUE;

}

$this->error = ‘Error deleting row.';

return FALSE;

}

 

// Builds SELECT part of the query

public function select($select = ‘*’, $protect_identifiers = TRUE)

{

if ($select != ‘*’ AND !empty($select))

{

$select = explode(‘,’, $select);

foreach ($select as $key => $field)

{

if ( !in_array($field, $this->fields, TRUE))

{

unset($select[$key]);

}

}

$select = !empty($select) ? $select : ‘*';

}

$this->db->select($select, $protect_identifiers);

return $this;

}

 

// Builds the select MAX part of the query

public function select_max($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_max($field, $alias);

}

return $this;

}

 

// Builds the select MIN part of the query

public function select_min($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

 

// Builds the select AVG part of the query

public function select_average($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

 

// Builds the select SUM part of the query

public function select_sum($field, $alias = ”)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->select_min($field, $alias);

}

return $this;

}

 

// Builds the JOIN part of the query

public function join($table, $join, $join_type = ”)

{

if ( !empty($table) AND !empty($join))

{

$this->db->join($table, $join, $join_type);

}

return $this;

}

 

// Builds the ORDER BY part of the query

public function order_by($field = ‘id’, $order = ‘ASC’)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->order_by($field, $order);

}

return $this;

}

 

// Builds the GROUP BY part of the query

public function group_by($field)

{

if (in_array($field, $this->fields, TRUE))

{

$this->db->group_by($field);

}

return $this;

}

 

// Builds the LIKE part of the query using the AND operator

public function like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->like($field, $match, $position);

}

return $this;

}

 

// Builds the OR LIKE part if the query using the OR operator

public function or_like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->or_like($field, $match, $position);

}

return $this;

}

 

// Builds the NOT LIKE part of the query

public function not_like($field, $match, $position = ”)

{

if (in_array($field, $this->fields, TRUE) AND !empty($match))

{

$this->db->not_like($field, $match, $position);

}

return $this;

}

 

// Builds the DISTINCT part of the query

public function distinct()

{

$this->db->distinct();

return $this;

}

 

// Builds the WHERE part of the query using AND and other operators

public function get_where($where, $protect_identifiers = TRUE)

{

if ((is_string($where) OR is_array($where)) AND !empty($where))

{

$this->db->where($where, $protect_identifiers);

}

return $this;

}

 

// Builds the OR WHERE part of the query using OR and other operators

public function get_or_where($where, $protect_identifiers = TRUE)

{

if ((is_string($where) OR is_array($where)) AND !empty($where))

{

$this->db->or_where($where, $protect_identifiers);

}

return $this;

}

 

// Builds the WHERE IN part of the query

public function where_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->where_in($field, $values);

}

return $this;

}

 

// Builds the WHERE NOT IN part of the query

public function where_not_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->where_not_in($field, $values);

}

return $this;

}

 

// Builds the OR WHERE NOT IN part of the query using the OR operator

public function or_where_not_in($field, $values)

{

if (in_array($field, $this->fields, TRUE) AND is_array($values) AND !empty($values))

{

$this->db->or_where_not_in($field, $values);

}

return $this;

}

}

There you have it. I don’t want to exaggerate, but the custom model class is definitely starting to look much more functional, mostly because of the incorporation of the chainable methods that you saw earlier in this article.

Feel free to introduce your own changes and improvements into this CodeIgniter model class, so you can arm yourself with a more solid grounding in using method chaining with PHP 5.

Final thoughts

Over this tenth chapter of the series, I demonstrated how useful the method chaining approach can be for expanding the functionality of the previous custom CodeIgniter model class. As you saw for yourself, adding a few common modifiers to a query is reduced to coding some modular chainable methods and nothing else.

In the penultimate tutorial, I’m going to finish developing the abstract model class by adding to it some extra methods, which will perform such additional tasks as validating incoming data, building error strings, and much more.

You know what my final suggestion is, but here we go again: don’t miss the next part!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan