+- 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 == 0 ? 0 : $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(reset: true)->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(reset: true)->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