Delete multiple records based on ids

Question

Delete multiple records based on ids

anddrzejb Jun '14

Created Jun '14	Last Reply Jun '14	Replies 3	Views 2505	Votes 0

anddrzejb 9.8k

Jun '14

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) { ...

Andy Myers
10.9k

Accepted
answer

edited Jun '14
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 :)

Dylan Anderson · Answer 1 · 2014-06-23T08:35:17-07:00

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();

Andy Myers · Answer 2 · 2014-06-24T01:07:10-07:00

Ahh well I'm wondering if:

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

Would give the number of affected records?

anddrzejb · Answer 3 · 2014-06-27T01:03:00-07:00

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.