Update/Delete many records at once?

[jockster]

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

[j4mie]

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

[jockster]

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?

[j4mie]

No problem, I reopened it.

[AsadR]

#38 resolves the delete part of the feature request.

[treffynnon]

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

[windbridges]

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

[treffynnon]

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

[windbridges]

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

[treffynnon]

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.