MY_Model base CRUD

[eluser]victorche[/eluser]
@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:

Code:

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

        if ( ! $comments)
        {
            return array();
        }
        else
        {
            return $comments;
        }
    }

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:

Code:

$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:

Code:

$query = $this->db->select('id', 'author', 'comment', 'added')->from('comments')
            ->order_by('id', 'desc')
            ->limit($limit, $offset)
            ->get();
foreach ($query->result_array() as $row)
{
                        $comments[] = array(
                                    'added' => nice_time($row['added']),
                                    ...
                        );
}
return $comments;

Which will work, but anyway how can I have the same result, using your functions and having smaller code as a result?

[eluser]Mark Croxton[/eluser]
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.

[eluser]dkenzik[/eluser]
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) ?

[eluser]victorche[/eluser]
[quote author="Mark Croxton" date="1281036573"]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.[/quote]
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 ...

[eluser]Mark Croxton[/eluser]
@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:

Code:

$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:

Code:

$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:

Code:

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

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

Code:

$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

Code:

$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'));

[eluser]victorche[/eluser]
@mark, please give me an example about this :]
Sorry, but I am still learning ...

[eluser]Mark Croxton[/eluser]

Code:

$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')
            )
        );

[eluser]victorche[/eluser]
[quote author="Mark Croxton" date="1281111101"]

Code:

$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')
            )
        );

[/quote]
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 :/

[eluser]Mark Croxton[/eluser]
get() returns a resultset array, so you can do whatever you like with it:

Code:

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


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

[eluser]MT206[/eluser]
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?

Code:

/**
* 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;
}