3

I currently have a table that has as primary key a composite value formed by id + foreign key. The id is auto-incremented and the foreign key references another table.

At the moment that I'm saving the data I only know about the foreign key value and I expect that the id returns to me from the database as an auto-increment number.

I understand that doctrine's does not support auto generation of id for a composite primary key so what can be done to allow with Doctrine to save the data and still have the auto-increment on part of the composite key.

Note, before submiting this question I had researched several other questions such as: Defining Composite Key with Auto Increment in MySQL and Doctrine: authorize NULL in a foreign composite key and I have also read Doctrine's documentation several times to see if I was missing something http://doctrine-orm.readthedocs.org/en/latest/tutorials/composite-primary-keys.html#use-case-1-dynamic-attributes

The main issue is that on Doctrine

Every entity with a composite key cannot use an id generator other than
“ASSIGNED”. That means the ID fields have to have their values set before
you call EntityManager#persist($entity).

To help with the issue here is an example of how the table is constructed:

create table composite_example (
    id int(11) not null auto_increment,
    fk_id int(11) not null,
    a_prop varchar(20),
    primary key (id, fk_id)
) engine=InnoDB default charset=utf8;

If I had to manually construct a MySQL query to insert into this table knowing the values of fk_id and a_prop I could do:

insert into composite_example (fk_id, a_prop) values (999, 'a_value');

And it would create a row with a proper value for id on the table.

How can I do the same behavior with Doctrine ? Does anyone knows any way or workaround to get the job done ?

Community
  • 1
  • 1
Mcloide
  • 422
  • 5
  • 16
  • why do you need the composite key for ? why don't you make just "id" a primary key ? – wonde Mar 30 '15 at 22:04
  • 1
    I can't really give you details but let me give you a scenario where you would use composite keys. In any given store you have POS and each of them you will have a database where you have customer 1 in POS 1 and customer 1 in POS 2. You can't affirm that customer 1 in POS 1 is the same person as customer 1 in POS 2 so, if you needed to keep those 2 POS in sync how would you do it? A solution for it is using composite keys. – Mcloide Mar 31 '15 at 12:43
  • Solution is to move autogenerated field to separate table and then join it with OneToOne relation. – vp_arth Oct 07 '17 at 16:14

1 Answers1

1

There is no easy solution for this matter but here are a few options:

1) You can remove composite key and use UUIDs as keys. This is not an easy solution because it will affect how much of your code needs to be changed, how long it will take to do the migration and how it can affect performance.

2) The other option would be something that was suggested by the Doctrine community that can be used in other ORM's as well: you work with a sequencer. For this solution you will need to create a table where you will store an entity identifier and the next column will be the last value for that identifier.

Example:

Table sequencer
id | entity_name | entity_id
88 | customers   | 77

The entity model would need to inform that the id is generated and on the pre-persist it will need to assign that id with a value that would come from the entity_id + 1 out of this table

select max(entity_id)+1 from sequencer where entity_name = 'customers';

This will cause 2 extra queries to be ran, one on the pre-persist and another on the post-persist which will update the sequencer table with the new value.

There are strategies to avoid concurrency on the sequencer table so it would always have the correct value and one of them is by locking the table o.0

3) You can write your own loaders and persister, but at this point you might be facing a change as big as the UUID implementation.

A lot of ORM's doesn't support this and if you are facing the same issue, you might have to consider one of the above.

Now, if you are working specifically with Doctrine I strongly suggest asking for help on the IRC channel. They were very helpful on providing me with some alternatives for this issue.

Mcloide
  • 422
  • 5
  • 16