Query Builder Order By

I'm trying to delete the oldest X number of records within my MYSQL database. But it appears that the query builder is not including the order_by into the query.

Code:

$this->db->from($table);
$this->db->where($where);
$this->db->order_by('last_changed', 'ASC');
$this->db->limit(1);
$this->db->delete();

log_message('ERROR', $this->db->last_query());

This is the query from 'last_query'

DELETE FROM `store_page_data`
WHERE `store_page_id` = '123'
AND `type` = 'DRAFT'
AND `client_id` = '1' LIMIT 1

If I run the query manually with order by, it works.

DELETE FROM `store_page_data` WHERE `store_page_id` = '123' AND `type` = 'DRAFT' AND `client_id` = '1' ORDER BY `last_changed` ASC LIMIT 1


I've check the documentation from the query builder and I can't see a reason why it's not being included. 

Any Ideas? 

@adampwe,

Try and see if query binding works better for you: https://www.codeigniter.com/user_guide/d...y-bindings

$sql = "DELETE FROM ? WHERE `store_page_id` = ? AND `type` = ? AND `client_id` = ? ORDER BY `last_changed` ASC LIMIT 1";
$this->db->query($sql, array('store_page_data',123,'DRAFT',1));

In MySQL, ORDER BY and LIMIT are only valid on single table deletes and Query Builder's delete() method only handles one table at a time anyway. It won't even consider a join() as would be needed for a multi-table delete operation. Additionally, if you examine the core source code you'll find no attempt to add an ORDER BY phrase to the query string. I don't know why it is structured this way. Perhaps it's a question of differences in the way various databases handle delete ops? It is what it is and appears to have been done on purpose.

One way to do what you want is to run a query to get the id of the record to be deleted. Then run delete() using that value. I assume that "store_page_id" is a unique record id.

I also assume that $where is defined something like this

PHP Code:

$where = array('store_page_id' => '123', 'type'  => 'DRAFT', 'client_id' => '1' 


PHP Code:

$id_query = $this->db
    ->where($where)
    ->order_by('last_changed', 'ASC')
    ->get($table, 1); // sets FROM and LIMIT clauses before running the query

// I always make sure that get() didn't return FALSE
if($id_query)
{
    // make sure $id is assigned a value
    if(empty($id = $id_query->row()->store_page_id))
    {
        return false;
    }

    // delete accepts "where" data in the second parameter. Has other inputs too - read the docs
    return $this->db->delete($table, array('store_page_id '=> $id));
} 


It's probably possible to use a sub-select in a query and do it all with one hit on the database. I always have a hard time constructing sub-selects using QB and so am reluctant to try it here. Perhaps somebody else can show us both how.