Single bean version of 'findForUpdate'

[alystair]

Thanks again for implementing findForUpdate, it has 'bean' useful - however I've found myself in a few situations where I only want to work with a single bean, similar to findOne. Since findForUpdate returns an array of beans, where the keys are the row IDs - I'm forced to use the following to easily mutate the lone bean (PHP7.3):

$itemArray = R::findForUpdate('items','used IS NULL LIMIT 1');
if (!empty($itemArray)) $item = $itemArray[array_key_first($itemArray)]; // Get lone bean

Additionally I noticed my IDE whines that array_key_first expects an array, not a RedBeanPHP\OODBBean - which is caused by a PHPDoc error. I submitted a tiny PR to fix it (#808).

Would be great if findForUpdate had a singular version, findOneForUpdate which returns a bean directly, like findOne.

[Lynesth]

Hey,

I suppose we could add a convenience function for findOneForUpdate. I'll wait to get @gabordemooij 's input on this before doing it though. Meanwhile you can use the plugin function to create your own pretty easily:

R::ext('findOneForUpdate', function($type, $sql = NULL, $bindings = array()) {
    $sql = R::getWriter()->glueLimitOne( $sql );
    $beans = R::findForUpdate($type, $sql, $bindings);
    return !empty($beans) ? reset($beans) : NULL;
});

[alystair]

Just out of curiosity does 'findForUpdate' also prevent row reads when others run it at the same time?

Eg. If multiple users attempt to run the code at the same time, is there a way to guarantee each gets a unique result/row locked unless no more are available?

[Lynesth]

R::findForUpdate simply adds FOR UPDATE at the end of your query so all of it is managed by your DBMS. You should check the one you're using if you want to know exactly how it works, but the idea is that it prevents reading the locked rows until the transaction is over, yes.

[alystair]

This entire time I could have been doing

R::find('items','used IS NULL LIMIT 1 FOR UPDATE');

instead of relying on helpers I guess? :)

[Lynesth]

Yep ! Or R::findOne would be more appropriate if you want a single bean :)

[alystair]

Well, if I change the above to findOne and remove the LIMIT 1 RedBean takes a dump (I'm really bad at SQL I guess)

RedBeanPHP\RedException\SQL: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1 -- keep-cache' at line 1 in C:\Users\alyst\Dropbox\development\barcraft.com\vendor\gabordemooij\redbean\RedBeanPHP\Driver\RPDO.php on line 194
--
1 | 0.0003 | 431720 | {main}( ) | ...\server.php:0
2 | 0.0034 | 649424 | require( '**************************' ) | ...\server.php:143
3 | 0.0257 | 5306064 | include( '**************************' ) | ...\index.php:22
4 | 0.4313 | 6832416 | RedBeanPHP\Facade::findOne( ) | ...\prize.php:41
5 | 0.4314 | 6832416 | RedBeanPHP\Finder->findOne( ) | ...\Facade.php:989
6 | 0.4314 | 6832480 | RedBeanPHP\Finder->find( ) | ...\Finder.php:196
7 | 0.4314 | 6832480 | RedBeanPHP\OODB->find( ) | ...\Finder.php:151
8 | 0.4314 | 6832480 | RedBeanPHP\Repository\Frozen->find( ) | ...\OODB.php:333
9 | 0.5021 | 6877800 | RedBeanPHP\Repository\Frozen->handleException( ) | ...\Repository.php:452

[Lynesth]

Yeah, my bad findOne adds LIMIT 1 at the end of the query which makes it invalid SQL because the " FOR UPDATE " must be at the end.