Query Builder Order By

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.