-1

Working with Symfony 3.x and Doctrine I have this problem: Entity "Foo" is defined as follows:

/**
 * @ORM\Entity
 * @ORM\Table(name="foo")
 */
class Foo
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $incr_int;

    ...
}

Leaving aside that $id and $incr_int will (allways?) be the same value I get the following error when creating a new Entity of type Foo:

An exception occurred while executing 'INSERT INTO foo (incr_int) VALUES (?)' with params [null]:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'incr_int' cannot be null

While this seem to make sense looking at the error itself I dont get how to fix it when creating Foo like this (which I believe is the standard way in Symfony?):

$foo = new Foo();
$em->persist($foo);     // $em is the entity manager
$em->flush();

Like I would expect it works if I delete the $incr_int field from Foo because the only remaining field $id is auto generated by increasing the last inserted id-value by 1. I was assuming this behaviour should be the same for the $incr_int field. Well... obviously it's not and I can't figure out why. Any help is highly appreciated.

user3440145
  • 793
  • 10
  • 34
  • 2
    Why do you need two AUTOINCREMENT fields? If you need this, it means you have bad logic in your database. AUTOINCREMENT field can be only **once** in a table. – nospor Jan 05 '17 at 12:11
  • @nospor Does this mean it is generally not possible to have two auto increment fields in one table? Well then... OK :-) What I really need is a unique integer field that holds incrementing values beginning at 1000... – user3440145 Jan 05 '17 at 12:18
  • Why do you need that 1000+? You know, that you can add 1000 number to ID in PHP? ;) Or that you can set `initialValue` for autoincrement? http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/basic-mapping.html – nospor Jan 05 '17 at 12:27
  • @nospor That's what I am doing atm (the +1000 in PHP) but I wanted to move this logic to DB/doctrine. initialValue only works for sequences which unfortunately are not available for doctrine with MySql (tried that as the very first thing). However: Now I have the problem that to retrieve the auto genrated id field I have to flush the entity manager first. But flushing requires me to set other required fields that depend on the id... How do I solve this? – user3440145 Jan 05 '17 at 12:41
  • First, set all fields you must set, do the flush, get the id, and update your second id column with +1000. – nospor Jan 05 '17 at 12:45
  • Anyway this seems to be a bad logic... there is something wrong in the way you thought the flows... are you sure you need to save to the database this Id + 1000? You can calculate in on the way when you call getIncrInt. Or you can set $incr_int as nullable to allow a null value... but again, there is something bad in the concrete implementation. – Aerendir Jan 06 '17 at 01:28
  • @nospor Thank you! – user3440145 Jan 06 '17 at 08:43
  • @Aerendir The final field in my real DB is a unique field generated out of some string-parts and including the id itself within the resulting (unique) string too. As the part-string values are predictable I could always generate this value again but I prefer to store it in the table too. – user3440145 Jan 06 '17 at 08:47
  • Why did I get a downvote for this? Not getting this on stackoverflow - why ppl downvote without explanations? – user3440145 Jan 06 '17 at 08:48
  • 1
    @user3440145 you are welcome :) And speaking about downvoting without explanations: it is common practice here so get used to it ;) – nospor Jan 06 '17 at 09:54

1 Answers1

1

There were 2 problems in my code (thanks to @nospor).

  1. A table can only have one auto increment field (and a second one doesn't make much sense anyways).
  2. When creating a new entity the id is generated when really updating the DB (flushing the entity manager).

I could solve this issue by removing the second auto increment condition and creating the Entity with the required fields (here only the auto generated id field) and then flushing it. After flushing I am able to retrieve the id and generate a second field's value holding a calculated value that depends on the id value as well.

user3440145
  • 793
  • 10
  • 34
  • Yes, but as this flow requires to queries, I suggest you to rethink the use of the ID and leave the second unique string without it... Just a suggestion :) – Aerendir Jan 06 '17 at 12:20
  • @Aerendir Hm y - but I need the second unique string to contain a sequential number. This seems to be the perfect reason to use the id field. Isn't it? Oterhwise I had to generate a sequence and store the last number and stuff - which seems more effort (and be less performant) then the way it is now... – user3440145 Jan 09 '17 at 10:53
  • Can you better explain what does "sequential" mean? Because it may mean something like "1, 2, 3, 4" or it may be sufficient somthing like "1, 3, 4, 7, 10...". The difference is very important: in the first case the current solution may work (then, there ever is time to refact the code to make it more performant). If you don't need, instead, that is an exact precise sequence, you can use \DateTime with micro seconds precision to be sure the sequential number is unique and sequential... – Aerendir Jan 09 '17 at 10:59
  • Example: `$time = \DateTime::createFromFormat('U.u', microtime(true));` and then `$time->format('YmdHisu')`. I use this last solution to generate the unique sequential numbers for the invoices. Maybe this can help you too. – Aerendir Jan 09 '17 at 11:00
  • Anyway, the use of the IDs doesn't guarantee the first kind of sequentiality, as some entities may be deleted, and so the result may not be a real sequence... – Aerendir Jan 09 '17 at 11:01
  • @Aerendir It is not necessary to have a sequence without "gaps" - so "1, 3, 4, 7, ..." is all right. Actually I pretty much need what you need for a unique invoice number as well. However having a "crypticaly looking" sequence number is discouraged (from the project owner). So using the id still seems to be good. As this will not often be processed and not in any kind of loop performace doesn't really matter to much. Thanks for your help! – user3440145 Jan 09 '17 at 15:48