Please give it a try at:
https://github.com/avenirer/CodeIgniter-MY_Model
This tutorial is a work in progress created at 2015.02.11 and updated at 2015.02.17. It’s tested with CodeIgniter 3.
Well… It’s that time when we should stop repeating ourselves. The DRY philosophy tells us that maybe we should use our time doing things faster than repeating the same steps for basic CRUD in our models. I’ve talked about the MY_Model subject in the past, but we can improve a lot on that.
A few years ago, Jamie Rumbelow wrote a great book named CodeIgniter Handbook in three volumes, in which he manages to bring Ruby philosophy in the world of CodeIgniter. In the book he talks about Convention over Configuration, trying to apply design principles like Don’t Repeat Yourself (DRY) and RESTful style to CodeIgniter.
He starts the book writing about his MY_Model, model which is freely accessible on Github (https://github.com/jamierumbelow/codeigniter-base-model). I would really advise you to try his MY_Model, and you won’t regret it.
This post is actually not about Jamie’s model, but about plagiarism, or, if you will, about learning how to make your own MY_Model by copying from the best.
Before we dive into the problem, let’s see the “rules of engagement”:
- one model must reflect the one table
- table names must be in plural
- model names must be in singular with the “_model” at the end
- each table should contain an id
Taking this into consideration, let’s start the project by creating a MY_Model inside the application/core directory.
We will start the file with a commented example of how to use the MY_Model, and then we will start copying Jamie Rumbelow’s model, creating our model having Jamie Rumbelow’s base model as source:
<?php defined('BASEPATH') OR exit('No direct script access allowed'); /** how to extend MY_Model: * class User_model extends MY_Model * { * public function __construct() * { * parent::__construct(); * } * } * **/ class MY_Model extends CI_Model { public function __construct() { parent::__construct(); } }
Now, you only have to extend the MY_Model in all your models.
<?php defined('BASEPATH') OR exit('No direct script access allowed'); class User_model extends MY_Model { public function __construct() { parent::__construct(); } }
Setting the database connection
There are times when we use different connections depending on what we need them for: for inserting, updating, or deleting we use a connection (which has writing rights), and for selecting we use another connection (which doesn’t have writing rights), so why not “injecting” the connection into our MY_Model.
If you look inside the application/config directory, you should see something in the lines of these:
$active_group = 'default'; $query_builder = TRUE; $db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'a_user', 'password' => 'a_password', 'database' => '', 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => TRUE, 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'autoinit' => TRUE, 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE );
Now, if you need to have two connections, you can simply add another $db key:
$db['writing'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'another_user', 'password' => 'another_password', 'database' => '', 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => TRUE, 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'autoinit' => TRUE, 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE );
If you need to know more about how to connect to multiple databases in CodeIgniter you could try another of my tutorials.
Now, going into our MY_Model, we will use a private method to setup the connection to the database:
class MY_Model extends CI_Model { /** * Select the database connection from the group names defined inside the database.php configuration file or an * array. */ protected $_database_connection = NULL; public function __construct() { $this->_set_connection(); parent::__construct(); } private function _set_connection() { isset($this->_database_connection) ? $this->load->database($this->_database_connection) : $this->load->database(); $this->_database = $this->db; } }
But what if, for some reason, we want to use a certain connection only for a method and not for the whole class? I’ve seen something similar in Eloquent where, if you want, you can use an on() method when doing a query. So let’s create one ourselves for MY_Model:
public function on($connection_group = NULL) { if(isset($connection_group)) { $this->db->close(); $this->load->database($connection_group); } return $this; }
Now, when we want something from a model and want to use another connection than the default set for the whole model we can simply do something like this: $this->user_model->on(‘another_connection’)->get_all().
But what if, after we’ve used this so called “another_connection” we want to use the default connection? For this we will need another method. Let’s call it reset():
public function reset() { if(isset($connection_group)) { $this->_database->close(); $this->_set_connection(); } return $this; }
Setting the table
If we respect the “rules of engagement”, the table name should be easy to retrieve from our MY_Model, by getting the plural of the models that extend MY_Model without the “_model” or “_m” substring. Also, we allow the users to set their own table name.
So just before the constructor we define another protected property:
protected $table = NULL;
And inside the constructor we call a private function named _fetch_table() that will return the name of the table:
$this->_fetch_table();
The function would look like this:
private function _fetch_table() { if (!isset($this->table) { $this->load->helper('inflector'); $this->table = plural(preg_replace('/(_m|_model)?$/', '', strtolower(get_class($this)))); } }
Setting the ID of rows
We will also allow the user to set up the column that will uniquely identify the row of data. But in the same time we will set it to be ‘id‘:
So, before the constructor we define a protected property $_primary:
protected $primary = 'id';
The created_at, updated_at and deleted_at fields
In most cases when working with databases, admins need to know when was a table record created, modified or “deleted”. For these situations, we should make sure we offer the users of MY_Model the possibility to fill these columns on the fly.
So, let’s define one new property named $timestamps. If this is set to true, it will know that it must fill these columns with values when needed
protected $timestamps = TRUE;
But what if the user of our MY_Model names the columns differently than “created_at”, “updated_at” and “deleted_at”? Well, for that possibility we can allow the user to define them inside the model that extends MY_Model:
$this->timestamps = array('made_at', 'modified_at', 'removed_at);
If we allow for this to happen, we should make sure we keep the fields name in our MY_Model. So, let’s define them before the constructor:
protected $_created_at_field; protected $_updated_at_field; protected $_deleted_at_field;
Now, inside the constructor we can make sure that if $timestamps is set to true or is an array, the model can find the accordingly fields in the table:
$this->_set_timestamps();
And now let’s define the method:
private function _set_timestamps() { if($this->timestamps === TRUE || is_array($this->timestamps)) { $this->_created_at_field = (is_array($this->timestamps) && isset($this->timestamps[0])) ? $this->timestamps[0] : 'created_at'; $this->_updated_at_field = (is_array($this->timestamps) && isset($this->timestamps[1])) ? $this->timestamps[1] : 'updated_at'; $this->_deleted_at_field = (is_array($this->timestamps) && isset($this->timestamps[2])) ? $this->timestamps[2] : 'deleted_at'; } return TRUE; }
The insert() method
The insert() method should accept an array as first parameter which will contain the values that should be inserted, if the array is a multidimensional one then it will insert more than one row:
public function insert($data) { // First of all let's make sure we receive an array... $data_as_array = (is_object($data)) ? (array)$data : $data; //now let's see if the array is a multidimensional one (multiple rows insert) $multi = FALSE; foreach($data_as_array as $element) { $multi = (is_array($element)) ? TRUE : FALSE; } if($multi === FALSE) { if($this->_database->insert($this->table, $data)) { $id = $this->_database->insert_id(); return $id; } } else { foreach($data as $row) { if($this->_database->insert($this->table,$row)) { $id[] = $this->_database->insert_id(); } } return $id; } return FALSE; }
The before_create, after_create, before_update, after_update, before_soft_delete, after_soft_delete, before_delete, after_delete triggers
We’ve created the insert() method, but what about the “created_at” field? Where does this one come into play? Well… we should make a before_create trigger which must add the created_at field in the $data that is received by the insert() method. For this, we just copy the code from Jamie Rumbelow’s great base model…
First of all let’s define the triggers before the constructor:
protected $before_create = array(); protected $after_create = array(); protected $before_update = array(); protected $after_update = array(); protected $before_get = array(); protected $after_get = array(); protected $before_delete = array(); protected $after_delete = array(); protected $before_soft_delete = array(); protected $after_soft_delete = array();
After we’ve defined them, we must define a function called trigger() that will take care of triggering the before_create, after_create, before_update, after_update, before_soft_delete, after_soft_delete, before_delete, after_delete:
public function trigger($event, $data = FALSE, $last = TRUE) { if (isset($this->$event) && is_array($this->$event)) { foreach ($this->$event as $method) { if (strpos($method, '(')) { preg_match('/([a-zA-Z0-9\_\-]+)(\(([a-zA-Z0-9\_\-\., ]+)\))?/', $method, $matches); $method = $matches[1]; $this->callback_parameters = explode(',', $matches[3]); } $data = call_user_func_array(array($this, $method), array($data, $last)); } } return $data; }
Now we define a function that will append a created_at field type to the data we are inserting:
protected function add_created($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_created_at_field})) { $row->{$this->_created_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_created_at_field])) { $row[$this->_created_at_field] = date('Y-m-d H:i:s'); } } return $row; }
As you can see, we first look if there already is defined a created_at field.
After we’ve done all this, we make sure that we call add_created() function before the creation of row(s). We do this inside the constructor, by adding it as another element of the $before_create array.
Returning to our insert() method…
Now, that we’ve created the triggers, we must modify the insert() method so that it will call before_create and after_create triggers:
public function insert($data) { foreach($data as $element) { if(is_array($element)) { $multi = TRUE; } } if(!isset($multi)) { $data = $this->trigger('before_create',$data); if($this->_database->insert($this->table, $data)) { $id = $this->_database->insert_id(); $return = $this->trigger('after_create',$id); return $return; } return FALSE; } else { foreach($data as $row) { $row = $this->trigger('before_create',$row); if($this->_database->insert($this->table,$row)) { $id[] = $this->_database->insert_id(); } } $return = $this->trigger('after_create',$id); return $return; } return FALSE; }
And that’s it…
The update() method
On the same note, let’s create the update() method.
Before starting to create the update() method, let’s not forget about the updated_at field. For this we will create an add_updated() method that will be used by the update() method:
protected function add_updated($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_updated_at_field})) { $row->{$this->_updated_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_updated_at_field])) { $row[$this->_updated_at_field] = date('Y-m-d H:i:s'); } } return $row; }
Now we call the method in a before_update trigger inside the controller:
$this->before_update[] = 'add_updated';
The update() method will receive an array or object as first parameter, and the name of the identifying column as a second parameter. What do I mean by this? If you want to update a row (or more than one row) and you know its ‘title’ you can do something like this:
$to_update = array('title' => 'My title', 'name' => 'My Name 2', 'date' => 'My date 2'); $this->post_model->update($to_update, 'title');
MY_Model will retrieve the value of ‘title’ column from the array and do a $this->db->where(‘title’,’My title’);
But what if we will allow more than one way of updating? We can allow the user to pass an array as second parameter which will be passed to the where method:
public function update($data, $column_name_where = NULL) { // First of all let's make sure we receive an array... $data_as_array = (is_object($data)) ? (array)$data : $data; //now let's see if the array is a multidimensional one (multiple rows insert) $multi = FALSE; foreach($data_as_array as $element) { $multi = (is_array($element)) ? TRUE : FALSE; } // if the array is not a multidimensional one... if($multi === FALSE) { $data = $this->trigger('before_update',$data); if(isset($column_name_where)) { if(is_array($column_name_where)) { $this->_database->where($column_name_where[0],$column_name_where[1]); } elseif(is_numeric($column_name_where)) { $this->_database->where($this->primary,$column_name_where); } else { $column_value = (is_object($data)) ? $data->{$column_name_where} : $data[$column_name_where]; $this->_database->where($column_name_where, $column_value); } } if($this->_database->update($this->table, $data)) { $affected = $this->_database->affected_rows(); $return = $this->trigger('after_update',$affected); return $return; } return FALSE; } // else... else { $rows = 0; foreach($data as $row) { $row = $this->trigger('before_update',$row); if(is_array($column_name_where)) { $this->_database->where($column_name_where[0], $column_name_where[1]); } else { $column_value = (is_object($row)) ? $row->{$column_name_where} : $row[$column_name_where]; $this->_database->where($column_name_where, $column_value); } if($this->_database->update($this->table,$row)) { $rows++; } } $affected = $rows; $return = $this->trigger('after_update',$affected); return $return; } return FALSE; }
The where() method
We’ve updated the records… but what about a where() method that will make everything easier for us. We will create a method that will accept parameters and will return the $this object:
public function where($where_col_array = NULL, $value = NULL) { if(isset($where_col_array)) { if(!is_array($where_col_array) && is_null($value)) { $this->_database->where($this->primary,$where_col_array); } elseif(isset($value)) { $this->_database->where($where_col_array, $value); } elseif(is_array($where_col_array)) { $this->_database->where($where_col_array); } } return $this; }
The delete() method
The delete() method is going to get a little more complex, because we will allow the users to have “soft deletes”, deletes that are not really deletes but only a fill of the deleted_at column.
First let’s deal with the deleted_at field. We start by creating the add_deleted() method:
protected function add_deleted($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_deleted_at_field})) { $row->{$this->_deleted_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_deleted_at_field])) { $row[$this->_deleted_at_field] = date('Y-m-d H:i:s'); } } return $row; }
Before the constructor we will set $soft_deletes to FALSE:
protected $soft_deletes = FALSE;
And in the constructor we will append the add_deleted function to the before_soft_delete trigger.
if($this->soft_deletes === TRUE) { $this->before_soft_delete[] = 'add_deleted'; }
Now we can return to our delete() method which will receive a $where parameter that, if not NULL, will be handled by the where() method we’ve just created.
/** * public function delete($where) * Deletes data from table. * @param $where * @return Returns affected rows or false on failure */ public function delete($where = NULL) { $this->where($where); $affected_rows = 0; if($this->soft_deletes === TRUE) { $query = $this->_database->get($this->table); foreach($query->result() as $row) { $to_update[] = array($this->primary => $row->{$this->primary}); } if(isset($to_update)) { foreach($to_update as &$row) { $row = $this->trigger('before_soft_delete',$row); } $affected_rows = $this->update($to_update, $this->primary); $this->trigger('after_soft_delete',$to_update); } return $affected_rows; } else { if($this->_database->delete($this->table)) { return $this->_database->affected_rows(); } } return FALSE; }
The get() method – returning one row…
Let’s now turn to our R in the CRUD: the “read” part. And we will start with the get() method, which will return only one row:
public function get($where = NULL) { $this->trigger('before_get'); if(isset($where)) { $this->where($where); } $this->_database->limit(1); $query = $this->_database->get($this->table); if($query->num_rows() == 1) { $row = $query->row(); $this->trigger('after_get'); return $row; } return FALSE; }
Pretty simple, right? If there is no $where parameter, it will return only the last row. But that doesn’t stop us to use the where() method inside the controller/model:
$returned = $this->user_model->where('username','avenirer')->get(); print_r($returned);
But let’s see how we can improve on what we have.
The limit() method
Why not, instead of using the query builder’s limit() method we map one of our own methods to it, so that it can be chained in our queries. So let’s create a method named limit(), which will receive two parameters: a limit of returned rows and an offset:
public function limit($limit, $offset = 0) { $this->_database->limit($limit, $offset); return $this; }
And now we simply replace $this->_database->limit(1) with $this->limit(1) in our get() method.
Return array or object?
Now, what if we want the returned result to be an array, and not an object? BUT what if we want for ALL our queries to return arrays?
For this we will first need to setup another property named $return_as which will accept as values either ‘array’ or ‘object’:
protected $return_as = 'object';
Now let’s create the methods that will allow us to change the way the results are returned:
public function as_array() { $this->return_as = 'array'; return $this; } public function as_object() { $this->return_as = 'object'; return $this; }
Doing these functions will allow us to also chain the return type in our queries. Now we create a function that will return the result. Jamie Rumbelow’s MY_Model has a great method named _return_type() so we will just copy from him:
protected function _return_type($multi = FALSE) { $method = ($multi) ? 'result' : 'row'; return ($this->return_as == 'array') ? $method . '_array' : $method; }
Now we go back to our get() method and call the above function on as result.
Returning to our get() method
Now, returning to our get() method, this is how it looks now:
public function get($where = NULL) { $this->trigger('before_get'); $this->where($where); $this->limit(1); $query = $this->_database->get($this->table); if($query->num_rows() == 1) { $row = $query->{$this->_return_type(FALSE)}(); $row = $this->trigger('after_get',$row); return $row; } return FALSE; }
Nice…
But what about the soft_deletes?
Well… the question here is: “If we have the $soft_deletes enabled, the read methods shouldn’t return the soft deleted rows unless asked for them, right?”
Laravel’s Eloquent has a few methods for the soft deletes: withTrashed(), onlyTrashed(), restore(), forceDelete(), trashed(). So why not make those in our MY_Model too?
The with_trashed() method
The with_trashed() method will make sure that the results will be returned with the soft deleted rows.
First of all we will create a property named $_trashed to ‘without’. This property can have three values to ‘only’ or ‘without’ or ‘with’, so that when a read query is made it will either show only the soft deleted rows, or only the not soft deleted rows or both.
Let’s first define the property before the constructor of MY_Model:
private $_trashed = 'without';
Now let’s create a with_trashed() method, an only_trashed() method:
public function with_trashed() { $this->_trashed = 'with'; } public function only_trashed() { $this->_trashed = 'only'; return $this; }
Once we’ve done this, we should create a private method named _where_trashed() that will create one more where statement, and be called by the where() method:
private function _where_trashed() { switch($this->_trashed) { case 'only' : $this->_database->where($this->_deleted_at_field.' IS NOT NULL', NULL, FALSE); break; case 'without' : $this->_database->where($this->_deleted_at_field, NULL); break; case 'with' : break; } $this->_trashed = 'without'; return $this; }
Now we will go to our where() method and do a small change that will call this private method:
public function where($where_col_array = NULL, $value = NULL) { if(isset($where_col_array)) { if (!is_array($where_col_array) && is_null($value)) { $this->_database->where($this->primary, $where_col_array); } elseif (isset($value)) { $this->_database->where($where_col_array, $value); } elseif (is_array($where_col_array)) { $this->_database->where($where_col_array); } } if($this->_trashed!='without') { $this->_where_trashed(); } return $this; }
Great.
The restore() method
The restore() method will “undelete” one or multiple rows. What that means is that it will set the “deleted_at” value to NULL:
public function restore($where = NULL) { $this->with_trashed(); $this->where($where); if($affected_rows = $this->_database->update($this->table,array($this->_deleted_at_field=>NULL))) { return $affected_rows; } return FALSE; }
The force_delete() method
This method will force the delete of a row if soft_deletes is enabled. This means that it won’t just fill the row with a value at deleted_at field, but simply delete it:
public function restore($where = NULL) { $this->with_trashed(); $this->where($where); if($affected_rows = $this->_database->update($this->table,array($this->_deleted_at_field=>NULL))) { return $affected_rows; } return FALSE; }
The trashed() method
The trashed() method will verify if a row is “deleted”, soft deleted:
public function trashed($where = NULL) { $this->only_trashed(); $this->where($where); $this->limit(1); $query = $this->_database->get($this->table); if($query->num_rows() == 1) { return TRUE; } return FALSE; }
Wow… we’re moving faaaast!
The get_all() method
The get_all() method will return more than one result (if there is a result). We will change take over the code from the get() method for this one:
public function get_all($where = NULL) { $this->trigger('before_get'); $this->where($where); $query = $this->_database->get($this->table); if($query->num_rows() > 0) { $row = $query->{$this->_return_type(TRUE)}(); $row = $this->trigger('after_get',$row); return $row; } return FALSE; }
The fields() method
But what if we want only a few columns, and not all of them? For this it would be nice to do another method named fields(), to which we can pass, the fields names, either as a string with the fields separated by comma, or as an array:
public function fields($fields = NULL) { if(isset($fields)) { $fields = (is_array($fields)) ? implode(',',$fields) : $fields; $this->_database->select($fields); } return $this; }
Cool…
The order_by() method
Going further, why don’t we have an order_by() method? Let’s create it, or just copy it from Jamie:
public function order_by($criteria, $order = 'ASC') { if(is_array($criteria)) { foreach ($criteria as $key=>$value) { $this->_database->order_by($key, $value); } } else { $this->_database->order_by($criteria, $order); } return $this; }
What does this method do? if we pass it an array it will take the key as field name and the value as ascending (ASC) or descending (DESC) order. Else, if the first parameter is a string, it will accept a second parameter as the ordering type.
To be continued with RELATIONSHIIIIIPS…
Now, let’s see the end result so far:
<?php defined('BASEPATH') OR exit('No direct script access allowed'); /** how to extend MY_Model: * class User_model extends MY_Model * { * public function __construct() * { * $this->_database_connection = group_name or array() | OPTIONAL * Sets the connection preferences (group name) set up in the database.php. If not set, it will use the * 'default' (the $active_group) database connection. * $this->table = table name | OPTIONAL (default: plural of model name) * Sets the name of the table. If nothing none is passed, the table name will be the plural of the model name * without the "_model" string (model name: User_model; table: users). * $this->primary = unique key | OPTIONAL (default: 'id') * $this->timestamps = TRUE | array('made_at','modified_at','removed_at') * If set to TRUE tells MY_Model that the table has 'created_at','updated_at' (and 'deleted_at' if $this->soft_delete is set to TRUE) * If given an array as parameter, it tells MY_Model, that the first element is a created_at field type, the second element is a updated_at field type (and the third element is a deleted_at field type) * $this->soft_deletes = FALSE * Enables (TRUE) or disables (FALSE) the "soft delete" on records. Default is FALSE * $this->return_as = 'object' | 'array' * Allows the model to return the results as object or as array * * parent::__construct(); * } * } * **/ class MY_Model extends CI_Model { /** * Select the database connection from the group names defined inside the database.php configuration file or an * array. */ protected $_database_connection = NULL; /** @var * This one will hold the database connection object */ protected $_database; /** @var null * Sets table name */ protected $table = NULL; protected $_fields; /** @var string * Sets default id column */ protected $primary = 'id'; /** @var bool | array * Enables created_at and updated_at fields */ protected $timestamps = TRUE; protected $_created_at_field; protected $_updated_at_field; protected $_deleted_at_field; /** @var bool * Enables soft_deletes */ protected $soft_deletes = FALSE; /** * The various callbacks available to the model. Each are * simple lists of method names (methods will be run on $this). */ protected $before_create = array(); protected $after_create = array(); protected $before_update = array(); protected $after_update = array(); protected $before_get = array(); protected $after_get = array(); protected $before_delete = array(); protected $after_delete = array(); protected $before_soft_delete = array(); protected $after_soft_delete = array(); protected $callback_parameters = array(); protected $return_as = 'object'; private $_trashed = 'without'; public function __construct() { parent::__construct(); $this->_set_connection(); $this->_fetch_table(); $this->_set_timestamps(); $this->before_create[] = 'add_created'; $this->before_update[] = 'add_updated'; $this->before_soft_delete[] = 'add_deleted'; } /** * public function insert($data) * Inserts data into table. Can receive an array or a multidimensional array depending on what kind of insert we're talking about. * @param $data * @return str/array Returns id/ids of inserted rows */ public function insert($data) { // First of all let's make sure we receive an array... $data_as_array = (is_object($data)) ? (array)$data : $data; //now let's see if the array is a multidimensional one (multiple rows insert) $multi = FALSE; foreach($data_as_array as $element) { $multi = (is_array($element)) ? TRUE : FALSE; } // if the array is not a multidimensional one... if($multi === FALSE) { $data = $this->trigger('before_create',$data); if($this->_database->insert($this->table, $data)) { $id = $this->_database->insert_id(); $return = $this->trigger('after_create',$id); return $return; } return FALSE; } // else... else { foreach($data as $row) { $row = $this->trigger('before_create',$row); if($this->_database->insert($this->table,$row)) { $id[] = $this->_database->insert_id(); } } $return = $this->trigger('after_create',$id); return $return; } return FALSE; } /** * public function update($data) * Updates data into table. Can receive an array or a multidimensional array depending on what kind of update we're talking about. * @param $data * @param $column_name_where * @return str/array Returns id/ids of inserted rows */ public function update($data, $column_name_where = NULL) { // First of all let's make sure we receive an array... $data_as_array = (is_object($data)) ? (array)$data : $data; //now let's see if the array is a multidimensional one (multiple rows insert) $multi = FALSE; foreach($data_as_array as $element) { $multi = (is_array($element)) ? TRUE : FALSE; } // if the array is not a multidimensional one... if($multi === FALSE) { $data = $this->trigger('before_update',$data); if(isset($column_name_where)) { if (is_array($column_name_where)) { $this->where($column_name_where[0], $column_name_where[1]); } elseif (is_numeric($column_name_where)) { $this->_database->where($this->primary, $column_name_where); } else { $column_value = (is_object($data)) ? $data->{$column_name_where} : $data[$column_name_where]; $this->_database->where($column_name_where, $column_value); } } if($this->_database->update($this->table, $data)) { $affected = $this->_database->affected_rows(); $return = $this->trigger('after_update',$affected); return $return; } return FALSE; } // else... else { $rows = 0; foreach($data as $row) { $row = $this->trigger('before_update',$row); if(is_array($column_name_where)) { $this->_database->where($column_name_where[0], $column_name_where[1]); } else { $column_value = (is_object($row)) ? $row->{$column_name_where} : $row[$column_name_where]; $this->_database->where($column_name_where, $column_value); } if($this->_database->update($this->table,$row)) { $rows++; } } $affected = $rows; $return = $this->trigger('after_update',$affected); return $return; } return FALSE; } /** * public function where($where) * Sets a where method for the $this object * @param $where_col_array * @param $value = NULL a $value is needed if the first parameter is a column name. * @return Returns $this object */ public function where($where_col_array = NULL, $value = NULL) { if(isset($where_col_array)) { if (!is_array($where_col_array) && is_null($value)) { $this->_database->where($this->primary, $where_col_array); } elseif (isset($value)) { $this->_database->where($where_col_array, $value); } elseif (is_array($where_col_array)) { $this->_database->where($where_col_array); } } if($this->_trashed!='without') { $this->_where_trashed(); } return $this; } /** * public function limit($limit, $offset = 0) * Sets a rows limit to the query * @param $limit * @param int $offset * @return $this */ public function limit($limit, $offset = 0) { $this->_database->limit($limit, $offset); return $this; } /** * public function delete($where) * Deletes data from table. * @param $where * @return Returns affected rows or false on failure */ public function delete($where = NULL) { $this->where($where); $affected_rows = 0; if($this->soft_deletes === TRUE) { $query = $this->_database->get($this->table); foreach($query->result() as $row) { $to_update[] = array($this->primary => $row->{$this->primary}); } if(isset($to_update)) { foreach($to_update as &$row) { $row = $this->trigger('before_soft_delete',$row); } $affected_rows = $this->update($to_update, $this->primary); $this->trigger('after_soft_delete',$to_update); } return $affected_rows; } else { if($this->_database->delete($this->table)) { return $this->_database->affected_rows(); } } return FALSE; } /** * public function force_delete($where = NULL) * Forces the delete of a row if soft_deletes is enabled * @param null $where * @return bool */ public function force_delete($where = NULL) { $this->where($where); if($this->_database->delete($this->table)) { return $this->_database->affected_rows(); } return FALSE; } /** * public function restore($where = NULL) * "Un-deletes" a row * @param null $where * @return bool */ public function restore($where = NULL) { $this->with_trashed(); $this->where($where); if($affected_rows = $this->_database->update($this->table,array($this->_deleted_at_field=>NULL))) { return $affected_rows; } return FALSE; } /** * public function trashed($where = NULL) * Verifies if a record (row) is soft_deleted or not * @param null $where * @return bool */ public function trashed($where = NULL) { $this->only_trashed(); $this->where($where); $this->limit(1); $query = $this->_database->get($this->table); if($query->num_rows() == 1) { return TRUE; } return FALSE; } /** * public function get() * Retrieves one row from table. * @param null $where * @return bool */ public function get($where = NULL) { $this->trigger('before_get'); $this->where($where); $this->limit(1); $query = $this->_database->get($this->table); if($query->num_rows() == 1) { $row = $query->{$this->_return_type(FALSE)}(); $row = $this->trigger('after_get',$row); return $row; } return FALSE; } /** * public function get_all() * Retrieves one row from table. * @param null $where * @return bool */ public function get_all($where = NULL) { $this->trigger('before_get'); $this->where($where); $query = $this->_database->get($this->table); if($query->num_rows() > 0) { $row = $query->{$this->_return_type(TRUE)}(); $row = $this->trigger('after_get',$row); return $row; } return FALSE; } /** * public function on($connection_group = NULL) * Sets a different connection to use for a query * @param $connection_group = NULL - connection group in database setup * @return obj */ public function on($connection_group = NULL) { if(isset($connection_group)) { $this->_database->close(); $this->load->database($connection_group); $this->_database = $this->db; } return $this; } /** * public function reset($connection_group = NULL) * Resets the connection to the default used for all the model * @return obj */ public function reset() { if(isset($connection_group)) { $this->_database->close(); $this->_set_connection(); } return $this; } /** * Trigger an event and call its observers. Pass through the event name * (which looks for an instance variable $this->event_name), an array of * parameters to pass through and an optional 'last in interation' boolean */ public function trigger($event, $data = array(), $last = TRUE) { if (isset($this->$event) && is_array($this->$event)) { foreach ($this->$event as $method) { if (strpos($method, '(')) { preg_match('/([a-zA-Z0-9\_\-]+)(\(([a-zA-Z0-9\_\-\., ]+)\))?/', $method, $matches); $method = $matches[1]; $this->callback_parameters = explode(',', $matches[3]); } $data = call_user_func_array(array($this, $method), array($data, $last)); } } return $data; } /** * public function with_trashed() * Sets $_trashed to TRUE */ public function with_trashed() { $this->_trashed = 'with'; return $this; } /** * public function with_trashed() * Sets $_trashed to TRUE */ public function only_trashed() { $this->_trashed = 'only'; return $this; } private function _where_trashed() { switch($this->_trashed) { case 'only' : $this->_database->where($this->_deleted_at_field.' IS NOT NULL', NULL, FALSE); break; case 'without' : $this->_database->where($this->_deleted_at_field, NULL); break; case 'with' : break; } $this->_trashed = 'without'; return $this; } /** * public funciton fields($fields) * does a select() of the $fields * @param $fields the fields needed * @return $this */ public function fields($fields = NULL) { if(isset($fields)) { $fields = (is_array($fields)) ? implode(',',$fields) : $fields; $this->_database->select($fields); } return $this; } /** * public function order_by($criteria, $order = 'ASC' * A wrapper to $this->_database->order_by() * @param $criteria * @param string $order * @return $this */ public function order_by($criteria, $order = 'ASC') { if(is_array($criteria)) { foreach ($criteria as $key=>$value) { $this->_database->order_by($key, $value); } } else { $this->_database->order_by($criteria, $order); } return $this; } /** * Return the next call as an array rather than an object */ public function as_array() { $this->return_as = 'array'; return $this; } /** * Return the next call as an object rather than an array */ public function as_object() { $this->return_as = 'object'; return $this; } /** * private function _return_type($multi = FALSE) * returns the result either as array or as object depending on $this->return_as value. Also if $multi is set to TRUE returns more than one result * @param bool $multi * @return string */ private function _return_type($multi = FALSE) { $method = ($multi) ? 'result' : 'row'; return ($this->return_as == 'array') ? $method . '_array' : $method; } /** * private function _set_timestamps() * * Sets the fields for the created_at, updated_at and deleted_at timestamps * @return bool */ private function _set_timestamps() { if($this->timestamps === TRUE || is_array($this->timestamps)) { $this->_created_at_field = (is_array($this->timestamps) && isset($this->timestamps[0])) ? $this->timestamps[0] : 'created_at'; $this->_updated_at_field = (is_array($this->timestamps) && isset($this->timestamps[1])) ? $this->timestamps[1] : 'updated_at'; $this->_deleted_at_field = (is_array($this->timestamps) && isset($this->timestamps[2])) ? $this->timestamps[2] : 'deleted_at'; } return TRUE; } /** * * protected function add_created($row) * * Receives a row of data and appends to it a created_at field type returning the row * * @param $row * @return mixed */ protected function add_created($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_created_at_field})) { $row->{$this->_created_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_created_at_field])) { $row[$this->_created_at_field] = date('Y-m-d H:i:s'); } } return $row; } /** * * protected function add_updated($row) * * Receives a row of data and appends to it a updated_at field type returning the row * * @param $row * @return mixed */ protected function add_updated($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_updated_at_field})) { $row->{$this->_updated_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_updated_at_field])) { $row[$this->_updated_at_field] = date('Y-m-d H:i:s'); } } return $row; } /** * * protected function add_deleted($row) * * Receives a row of data and appends to it a deleted_at field type returning the row * * @param $row * @return mixed */ protected function add_deleted($row) { if($this->timestamps === TRUE || is_array($this->timestamps)) { if(is_object($row) && !isset($row->{$this->_deleted_at_field})) { $row->{$this->_deleted_at_field} = date('Y-m-d H:i:s'); } elseif(!isset($row[$this->_deleted_at_field])) { $row[$this->_deleted_at_field] = date('Y-m-d H:i:s'); } } return $row; } /** * private function _fetch_table() * * Sets the table name when called by the constructor * */ private function _fetch_table() { if (!isset($this->table)) { $this->load->helper('inflector'); $this->table = plural(preg_replace('/(_m|_model)?$/', '', strtolower(get_class($this)))); } } /** * private function _set_connection() * * Sets the connection to database */ private function _set_connection() { isset($this->_database_connection) ? $this->load->database($this->_database_connection) : $this->load->database(); $this->_database = $this->db; } }
really wonderful review.
Hello,
I am trying to implement many to many relation, but as result I am getting only pivot_table_foreign_key and foreign_table_local_key in result set. In plain sql I would use something like:
// $query = $this->db->query(“select articles.*, tags.* from articles_has_tags join articles on article_id = articles_article_id join tags on tag_id = tags_tag_id ORDER BY $order_by DESC LIMIT $limit”);
// $query = $query->result();
In respective models, pivot table and foreign keys are named as well using right way description.
What should I do to get all data from foreign table?
Well… you didn’t offer me enough details (like showing your models).
Thank you for reply.
I found out that `$this->_select` doesn’t take `”*”` from some point. Also revealed that I can use fields argument in `with_relationparameter()` to get wanted values.
In my project, I changed some code to be able to use native workbench created many to many tables that are named table1_has_table2 although it shouldnt be problem since I assigning specific names of tables and name of keys in models. I will try with default installation of MY_Model and let you know if it is working as well or I have some issues.
It would be best to have the latest MY_Model, as I think the last thing I did was exactly that problem with the _select property…
Sorry for bothering.
It is all working well when I reassessed `get_relate` property. :blush:
Btw, have you checked that line 1245 which doesn’t fit documentations?
Best regards,
Thanks for all the great tutorials really has given me the confidence to stay with Codeigniter.
You’re welcome!
How do use update method , when updating related data in 2 or more tables ,
let’s say the user name , and phone? stored in 2 tables but related.
Here https://github.com/avenirer/CodeIgniter-MY_Model i saw an example with only updating one table.
How do i make my model to update let’s say
Name, Phone number, prefix, Address, all inserted in 3 distinct tables?
You can’t… or you can if you change the MY_Model accordingly. But this is not an ORM, and has its limitations.