CodeIgniter Forums
orderBy() function is not working when delete() function is used in query builder. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: orderBy() function is not working when delete() function is used in query builder. (/showthread.php?tid=79295)



orderBy() function is not working when delete() function is used in query builder. - sustech - 05-25-2021

PHP Code:
$sql "DELETE FROM `table_name` WHERE `feed_id` = " $feed_id " ORDER BY `date` ASC LIMIT 25"// query to be executed 



PHP Code:
$this->db->table('table_name')
->
where('feed_id'$feed_id)
->
orderBy('date''ASC')
->
limit(25)
->
delete();
echo 
$this->db->showLastQuery(); // DELETE FROM `table_name` WHERE `feed_id` = 15 LIMIT 25 


orderBy() function is not working when delete() function is used in query builder.


RE: orderBy() function is not working when delete() function is used in query builder. - paliz - 05-25-2021

when you delete some data you dont need sort or order 
PHP Code:
/**
     * edit function
     * @method : DELETE with params ID
     */
    public function delete($id null)
    {


        $contactFileModel = new ContactMediaModal();
        $handy = new CustomFileSystem();
        $id = ($id == $id);

        if ($id == 0) {

            $isExist $contactFileModel->where(['contact_id' => $this->request->getGet('foreignKey')])->findAll();
            $target = array('contact_id' => $this->request->getGet('foreignKey'));
        } else {
            $isExist $contactFileModel->where(['id' => $id])->findAll();
            $target = array('id' => $id);
        }


        if ($isExist) {
            $contactFileModel->where($target)->delete();
            foreach ($isExist as $path) {

                $handy->removeSingleFile(ROOTPATH $path->path);
            }


        }

        return $this->respond([
        ], ResponseInterface::HTTP_OK,  lang('Common.api.remove'));

    



RE: orderBy() function is not working when delete() function is used in query builder. - sustech - 05-25-2021

It's business logic. I need to delete only 25 records in ascending order of `date` column.


RE: orderBy() function is not working when delete() function is used in query builder. - stopz - 05-26-2021

I've looked in to your situation and i could say that ->orderBy not taken into delete query account is a bug.

In order to benefit from CodeIgniter deleted_at perks: your temporary solution for you would be along thous lines:

PHP Code:
$deleted_results array_map(
    # Delete each row in select query result 1 by 1.
    fn ($x) => $this->db->table('my_table')->where('primary_key'$x['primary_key'])->limit(1)->delete(),

    # Query to select rows for deletion.
    $this->db->table('my_table')
        ->select('primary_key')
        ->where([
            'my_type' => 1
            
])
        ->orderBy('updated''DESC')
        ->get(resettrue)->getResultArray() ?? []
); 

Hope it helps someone!


RE: orderBy() function is not working when delete() function is used in query builder. - sustech - 05-26-2021

(05-26-2021, 11:49 AM)stopz Wrote: I've looked in to your situation and i could say that ->orderBy not taken into delete query account is a bug.

In order to benefit from CodeIgniter deleted_at perks: your temporary solution for you would be along thous lines:

PHP Code:
$deleted_results array_map(
    # Delete each row in select query result 1 by 1.
    fn ($x) => $this->db->table('my_table')->where('primary_key'$x['primary_key'])->limit(1)->delete(),

    # Query to select rows for deletion.
    $this->db->table('my_table')
        ->select('primary_key')
        ->where([
            'my_type' => 1
            
])
        ->orderBy('updated''DESC')
        ->get(resettrue)->getResultArray() ?? []
); 

Hope it helps someone!

Thanks for your help. This code is overkill and heavy on database performance for such simple logic. I have submitted this bug in Codeigniter github issue. Moderator said this is not a bug, It's a missing feature.


RE: orderBy() function is not working when delete() function is used in query builder. - salain - 05-27-2021

Hi,

You should use  whereIn() with a subquery

Put your orderBy and limit clause in the subquery.

Look at the documentation here

Regards