Thread

@Mark, thanks! But anyway sorry for my stupid questions ... There are some cases when these functions are really great. Anyway I am having trouble with a simple query about getting comments for a specific post. I mean ... I can get them really easy with your functions, like:
function get_comments($id)<br />   {<br /> $comments = $this->with('comments')->get(<br /> array(<br /> 'post' => $id,<br /> 'fields' => array('author', 'comment', 'added')<br /> )<br /> );</p> <p> if ( ! $comments)<br /> {<br /> return array();<br /> }<br />       else<br />       {<br /> return $comments;<br /> }<br />   }
But what is the problem here ... I have everything really raw. For example this gives me for “added” datestamp, which I really want to transform in something nicer like “2 days ago”. I mean, this way for doing things is really quick, gentle. But in this case, I am getting everything ready in an array and I can not do something like:
$added = nice_time($row[‘added’]);
So I have to use a regular query, because I don’t think I can do something with your set of functions. Like:
$query = $this->db->select(‘id’, ‘author’, ‘comment’, ‘added’)->from(‘comments’)<br />         ->order_by(‘id’, ‘desc’)<br />         ->limit($limit, $offset)<br />         ->get();<br /> foreach ($query->result_array() as $row)<br /> {<br /> $comments[] = array(<br /> 'added' => nice_time($row['added']),<br /> ...<br /> );<br /> }<br /> return $comments;
Which will work, but anyway how can I have the same result, using your functions and having smaller code as a result?
:(

get() returns a resultset, so you could iterate through it using foreach() 😊

But to be honest I think of date formatting as display logic, so I’d use nice_time() in the view instead. The controller shouldn’t need to worry about how something looks.

Here’s a quick question, and probably something I overlooked, so feel free to berate me as necessary…

What’s the proper way to utilize OR and AND (or a combination of each) in the ->where(array()) ?

And related… can the ->where() just accept a literal statement, similar to ->select(“foo.bar”,false) ?

get() returns a resultset, so you could iterate through it using foreach() 😊

But to be honest I think of date formatting as display logic, so I’d use nice_time() in the view instead. The controller shouldn’t need to worry about how something looks.

Thanks, @Mark! But I am using the template parser, so my date in the views looks just like {added}
Can you please give me an example how would you do it in this case? The query I mean ...

@dkenzik

Except for the ones I’ve overloaded (where(), select() etc), all the other CI Active record methods are available to use, you just don’t need to reference the $db object and they can be chained. Eg:

$this->where(array('age >=' => '30'))->where_not_in('username', $names);

You can pass a hardcoded string to where(), optionally passing the value in the second parameter if you want CI to protect the table names and fields with backticks:

$this->where('age >= 30');

// this is the same as above except the field will be enclosed within backticks:
$this->where('age >=', '30');

If you do this though MY_Model won’t check that ‘age’ actually exists as a column in the current table, and MY_Model will not record that the table has been used in the query.

If you pass an array you will get the integrity checking, and table name and field will be enclosed within backticks. This is the way I recommend you use where() whenever possible:

$this->where(array('age >=' => '30'));

If you want to generate a WHERE IN(value1, value2) then the value passed can be an array:

$this->where(array('age' => array('30', '31', '32')));

You can also hardcode the table names if you wish; if you do this then as before no integrity check will be done on the column. You will want to do this if you want to use SQL functions like LOWER, DATE_FORMAT etc

$this->where(array('LOWER(LEFT(my_table.last_name, 1)) =' => 'd'));

//note this won't work because we've left off the table name so My_Model will attempt to parse the statement and escape the field:
$this->where(array('LOWER(LEFT(last_name, 1)) =' => 'd'));

@Mark, please give me an example about this :]
Sorry, but I am still learning ...

$comments = $this->with('comments')->get(
            array(
                'post' => $id,
                'fields' => array('author', 'comment', 'FROM_UNIXTIME(comments.added,"%Y %D %M %h:%i:%s %x") as added')
            )
        );

Thanks, Mark but ... I don’t just need it like this. I need to use a small function which will turn the date into “2 days ago”. So your example is purfect, but not exactly what I need :/

get() returns a resultset array, so you can do whatever you like with it:

$comments = $this->with('comments')->get(
   array(
      'post' => $id,
      'fields' => array('author', 'comment', 'added')
   )
);

 
foreach ($comments as &$row)
{
   $row['added'] = nice_time($row['added']);
}

Great base model. I really like how you address joins where almost all other my_models don’t. Hopefully you keep this up. I am going to use this in my first big project that I am working on.

One thing I would like to run by you is whether or not this is the best way to handle this or not. I use a soft delete for most things where I just switch between 1 and 0 in a deleted field to ‘delete’ a record. I have created a slightly modified version of your update method where the $data variable is instead hard-coded to the ‘deleted’ field. Am I going about this correctly?

/**
* Removes record(s) by setting 'deleted' row to '1' for a record
*
* @param array $where
* @param string $table
* @return boolean
*/
public function soft_delete($where=null, $table=null)
{
if ($where == null) return FALSE;
if ($table == null) $table = $this->_table;

// reset query values and set our primary table
$this->with($table);

// assume primary key id of record has been passed
if (!is_array($where)) $where = array($this->$table->pk => $where);

// make where conditions, force use of the table name
$this->where($where, $table, false);

if (array_search('deleted', $this->$table->fields) === FALSE)
{
unset($data[$key]);
}

// do update
if ($this->db->update($this->$table->table, array('deleted' => 1))
{
$this->_affected_rows = $this->db->affected_rows();
return true;
}
else return false;
}

I have a serious problem with this set of functions now. I am having a common_model.php where I am doing some common stuff, used all over the site. Like:
class Common_model extends MY_Model<br /> {</p> <p> function __construct()<br /> {<br /> parent::__construct();</p> <p> $this->load_table('links');<br /> }</p> <p>  function get_menu()<br />   {<br /> return $this->with('links')->get(array('fields' => 'name'));<br /> }
This is an example of a small function, which generates an array of my top menu link names. The common model is loaded in MY_Controller.php and this way I can access $this->links on every other controller easily. This way I am not repeating my code and I have the top menu all over the site.
Anyway there is a case, when I need to use the same table ‘links’ in another model. For example to get the links description.
class Categories_model extends MY_Model<br /> {</p> <p> function __construct()<br /> {<br /> parent::__construct();</p> <p> $this->load_table('links'); // Maybe here I am making some kind of double load?<br /> }</p> <p>  function get_description($name)<br />   {<br /> return $this->with('links')->get_one(<br /> array(<br /> 'name' => $name,<br /> 'fields' => 'description'<br /> )<br /> );<br /> }
In this case, when I load the “Categories” controller, everything breaks. I know that ‘links’ table is loaded once in common_model.php and maybe this makes it some kind of globally loaded. But this way my array for the top menu looks like:
Table Object ( [table] => links [alias] => links [fields] => Array ( [0] => id [1] => name [2] => description ) [pk] => id [_related:Table:private] => Array ( ) )
Which as you can see is totally wrong :/
If I try to remove:
$this->load_table(‘links’);
In either Categories_model or Common model, the result is:
Fatal error: Call to a member function related() on a non-object in \application\core\MY_Model.php on line 759
Any ideas? :/

@victorche

if you need common functions I would seriously advise using a library to do it and autoloading it. You can still have your menu functions in model that extends MY_Model, and the library can act as a wrapper for them. Alternatively look a modular extensions (HMVC) or using a widget (‘intelligent view partials’).

Subclassing MY_Model with another common model will have unpredictable results since you will be instantiating 2 instances of the Table class (as you have found). Arguably I should have made Table a singleton; I’ll look at doing that in the next version.

@victorche

Ignore that, I didn’t quite get what you were doing.

Load the links table normally in your common model but use an alias for your link table in your other models:

$this->load_table('links', 'links_alias');

//Now refer to your table like this
$this->with("links_alias")

@MT206

Looks good except for this bit which will throw an error ($data doesn’t exist)

if (array_search('deleted', $this->$table->fields) === FALSE)
{
unset($data[$key]);
}

You might as well return false if a ‘deleted’ column doesn’t exist in your table:

if (array_search('deleted', $this->$table->fields) === FALSE)
{
return false;
}

Oops yeah I must have skipped over that part. Thanks for taking a look.