Update/Delete many records at once?

Question

Update/Delete many records at once?

jockster opened this issue 13 years ago · 9 comments

Hi again Jamie,

I tried the following, which didn't work. Is there any current solution to my issue?

    $existingSessions = ORM::for_table('sessions')->where_lt('expire', date("Y:m:d H:i:S", time()))->find_many();

    $existingSessions->delete();

Thanks

Answer 1 · 2011-02-17T14:01:47.000Z

No, unfortunately this won't work at the moment. find_many returns a simple PHP array of ORM instances, one for each row. To delete them all, you'd need to call:

foreach ($existingSessions as $session) {
    $session->delete();
}

I'm aware that this isn't ideal, as it generates n queries. This problem has been discussed before, and it's on my roadmap. Ideally, I'd want the syntax to be:

ORM::for_table('sessions')->where_lt('expire', date("Y:m:d H:i:S", time()))->delete();

Similarly, the following should be possible:

$results = ORM::for_table('person')->where('name', 'Fred');
$results->name = 'Bob';
$results->save();

This should issue UPDATE person SET name="Bob" WHERE name="Fred";

This will require quite a lot of refactoring, but I think (in principle) it should be possible. I'll keep this ticket open as a feature request. Unfortunately, I'm very busy at the moment so I can't give an ETA.

Jamie

Answer 2 · 2011-02-17T14:12:34.000Z

Hi Jamie,

No stress at all! Big thanks for both your initiative with idiorm as well as your fantastic support!

Take care!
Jay

EDIT:
Whoa, I accidentally closed it. Maybe you can sort that out so it stays open for you?

Answer 3 · 2011-02-17T14:16:58.000Z

No problem, I reopened it.

Answer 4 · 2011-09-14T18:35:07.000Z

#38 resolves the delete part of the feature request.

Answer 5 · 2013-01-15T12:23:51.000Z

To delete multiple records you should use the delete_many() method in Idiorm. For all else you can now use result sets in commit 5a6d20c

Answer 6 · 2013-02-06T13:15:43.000Z

Hi. Is it possible to update many records at once? Something like UPDATE table SET foo='bar' WHERE id IN (1,2,3) ?

Answer 7 · 2013-02-06T13:30:07.000Z

You can do this with raw_execute: https://github.com/j4mie/idiorm/blob/master/idiorm.php#L320 or http://idiorm.readthedocs.org/en/latest/querying.html#raw-queries

Answer 8 · 2013-02-06T16:53:47.000Z

lol, I can do any query with raw_execute, but then why we need ORM? ) I meant something like for_table()->where_in('id',array(1,2,3))->set('foo','bar')->save();

Answer 9 · 2013-02-06T17:01:01.000Z

Hmm, yes, I know what you meant. My answer still stands though, but there are two other less appealing options:

Use the ResultSet and perform the updates that way: http://idiorm.readthedocs.org/en/latest/querying.html#as-a-result-set
Loop over the results and perform your update that way

Idiorm is deliberately simple and this is one of those features that has been deliberately omitted.

If you have a good idea of how to implement it (whilst supporting PHP 5.2) then please do open a pull request.