3

We built a shop system with symfony2 using doctrine which has to generate unique serial order numbers. There's an Order entity which originally had a property orderNumber The Order entity is our "shopping session", so not only submitted orders are persisted but every time a user puts sth. into his cart. The orderNumber is default null and should only be set when the user submits an order. In plain MySQL I would have done sth. like this:

INSERT INTO orders (orderNumber, [...])
SELECT MAX(orderNumber) + 1, [...] FROM orders;

But to achieve this in doctrine I would have to do a table write lock, select the max orderNumber, set it to order entity, persist and then unlock the table. I don't wanted to do that (cause of the table lock) so the quick workaround was to kick the orderNumber property and just show the id as order number. But that's not a nice solution, the order numbers have gaps and getting higher and higher (after 4 months we have an auto increment value of 140k and the order number should not have more than 6 chars, so we need a solution before running into this limit).

To create a mapped superclass and split the Order entity into separate entities and tables for temporary and submitted orders would lead to too much code changes. So the next idea is to create a separate OrderNumber entity with an auto increment field and just set this to the Order entity when the user submits the order. This seems to be a way to do it without much changes.

But maybe there's a better way to solve this, what's the best solution to do it the doctrine way?

edditor
  • 150
  • 1
  • 10
  • If your field is auto incrementing then surely the way to do this is to omit the order number from your query data and retrieve the inserted ID fr that row following the query execution? In that way MySQL will simply perform the increment for you without the need for externally influenced row locks? – puppyFlo Feb 16 '16 at 11:19
  • I think you misunderstood the problem or I don't get it how this would solve it... – edditor Feb 16 '16 at 12:02
  • If orderNumber is unique then no need for a table lock. If two competing numbers do make it in then one will fail. You will need to recover from the exception but that should be a rare case. But @JanMares answer is a better approach. – Cerad Feb 16 '16 at 12:52
  • @Cerad OK, this would work. But I dislike this solution because it adds extra logic outside the place where it should be. I prefer a docrine only solution. – edditor Feb 16 '16 at 14:31

2 Answers2

2

It is considered as a drawback in e-commerce when auto increment is used for order numbers. The reason is that your competition can easily track these numbers and more or less know, how many orders you had for a period of time. They can use this knowledge to their benefit. That is why there was a request in our company to generate random order numbers. We did this by "scrambling" the ID retrieved from auto increment to generate a 6 character identifier containing numbers and letters (when you use letters, you can easily fit more then million orders to 6 chars, payment gateways should be OK with letters). This was done in a deterministic way, so this number can be converted back to the ID (although this is not strictly necessary).

Your solution with a separate table for order numbers seems pretty fine to me. You can also have a unique constraint on orderNumber and generate a random number or hash of a random number(seed should reflect the actual time and users session id, to limit probability of collisions) and try to update the entity. If it failed you will try to regenerate. You should then set a maximum number of times this can fail, so it would not cycle forever, if there is a different problem.

Jan Mares
  • 795
  • 10
  • 22
1

Has described in this presentation Doctrine ORM Good Practices and Tricks

I want to suggest you to avoid auto-generated identifiers and so to use an UUID instead with, as example the ramsey/uuid Library.

So, As Example, Your Order entity can be some similar to

 ......
/**
 * @ORM\Id
 * @Column(type="string")
 * @GeneratedValue(strategy="NONE")
 */
protected $id = null;

......

public function __construct()
{
    $this->id = Uuid::uuid4();
}

So this is accessible before you persist it on the database.

Better inspiration on the (Great!) talk of Pivetta

Hope this help

Matteo
  • 37,680
  • 11
  • 100
  • 115