Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Delete multiple records based on ids

I am trying to delete multiple records based on a primary key. The only way I was able to achieve this was through PHQL

            $aIDToDel = preg_filter('/chkcntID_*/', '$1', array_keys( $_POST ));
            $aIDToDel = array_map('intval', $aIDToDel);

            $phql = "DELETE FROM Country WHERE cntID IN (".$sIDToDel.")";
            $this->modelsManager->executeQuery($phql);

How can I get result after executing this query (i.e. 20 records affected)? Is there any other way of doing this? I found a nice way of doing this but it is for MongoDB (I am using MySQL)

            $countries = Country::find([['cntID' => ['$in' => $aIDToDel]]]);
            foreach ($countries as $country) {
                if ($country->delete() == false) { ...


6.8k
Accepted
answer
edited Jun '14

The following is a copy of something I've used; I have changed it from the orginal Orders to Country for example purposes

    $id_in = implode(',', $request['id']); // Produces a list of ids from an array generated by checkboxes in a form, e.g. "1,9,25,57"

    $countries = Country::find('id IN ('.$id_in.')')->filter(function($country){
            if($country->example_field == "Somthing I want to check first") { // optional
                $country->delete();
                return $order;
            }
        }
    });

    $count = count($countries);
    $this->flash->success(($count != 0 ?  $count : 0) . " ".($count == 1 ? "country" : "countries")." deleted.");
    # etc etc

Hope there is something you can use here :)

You can call delete() right from find(), so this should work:

$aIDToDel = preg_filter('/chkcntID_*/', '$1', array_keys( $_POST ));
$aIDToDel = array_map('intval', $aIDToDel);

Country::find('id IN ('.$aIDToDel.')')->delete();

Ahh well I'm wondering if:

    $Countries = Country::find('id IN ('.$aIDToDel.')')->delete();
    $AffectedRows = count($Countries);

Would give the number of affected records?



5.5k

quasipickle: Thanks for your suggestion, I will probably use it, however as pixiesky said, it doesn't return number of deleted records, only true/false for status of the delete method.

pixiesky: I partially used your solution, but I had to separate the find and delete - had to put the delete() into try/catch to get the records that were not deleted due to integrity locks.

So at the end, my code looks like this:

    $sIDToDel = implode(",", $aIDToDel);

    $countries = Country::find('cntID IN ('.$sIDToDel.')');
    $count = count($countries);
    $countAfterDel = 0;

    foreach ($countries as $country)
    {
        try
        {
            $countAfterDel += $country->delete();
        } catch (Exception $e) {
            $aErrMsgs['"'.$country->cntName.'"'] = $e->getMessage();
        }
    }

Now I have a problem with locking records, however I will first try to find the solution on my own.

Thanks for the help.