I have two database tables: visitors and passes. Visitors includes a field for pass_id, which refers to one of the records in the passes table.
The passes table is pre-populated with a list of valid passes.
Each visitor must be randomly assigned a pass ID on arrival, with the expectation that no other visitors will be allocated that same ID (until the associated visitor record is signed out).
The naïve solution simply performs a find() to pull out a randomly unassigned pass ID and then passes that ID through to the subsequent save() by value, but that doesn't prevent the same pass from being allocated multiple times by chance (especially as the number of concurrent users goes up, or the pool of available passes gets smaller).
In a pure-MySQL implementation, I would do a sub-query in the INSERT, such that both operations happen at once instead of in two discrete DB calls. The relevant MySQL documentation makes it fairly clear that transactions alone won't prevent external database changes from happening after a SELECT query, so I can't use Cake's built-in transaction support for this.
Also in pure MySQL I could use triggers to get the value dynamically (and not even inform Cake of it) but if I exhaust the list of possible pass IDs I'd want to be able to show a meaningful error instead of getting a SQL insert failure on save (plus we'd like to remain database agnostic if possible).
Is it possible to do this nicely in CakePHP without needing to resort to using Model::query()?
