CodeIgniter Question: How to delete records using ActiveRecord with mySQL function as WHERE condition

Question

I have a table named ChatSessions where I keep track of active users in the chatroom. I need to prune expired user sessions from the table every 10 minutes. Using pure php-mysql is plain simple but I'm totally clueless how to convert this into ActiveRecord in CodeIgniter. The plain SQL query is below:

SELECT *
FROM `ChatSessions`
WHERE `SessionExpires` < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )

Can anybody tell me what is the equivalent code in CodeIgniter using ActiveRecord?

Thanks in advanced.

Answer 1

Converting it to ActiveRecord if you already have the SQL seems like a waste of time. I'd just execute the SQL directly.

$sql = "DELETE FROM ChatSessions 
        WHERE SessionExpires < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
$this->db->query($sql);

Answer 2

The following code should do it:

// You can use custom strings in the where() function
$where = "SessionExpires < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
// although you may need to set the third parameter to FALSE in order to stop CI from protecting your fields.
$this->db->where($where, null, false);
$this->db->delete('ChatSessions');

You could also try the following (but I don't know if this will work):

$where_condition = "DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
$this->db->where("SessionExpires <", $where_condition, false);
$this->db->delete('ChatSessions');