2

I am struggling with choosing the right mapping for the following category like scenario : 

The food entity has a composed primary key made of 3 fields plus a name field:
╔════════╦═══════╦════════╦════════════════╗
║ family ║ class ║ sector ║ name           ║ - family INT UNSIGNED NOT NULL
╠════════╬═══════╬════════╬════════════════╣ - class INT UNSIGNED DEFAULT NULL
║ 1      ║ NULL  ║ NULL   ║ Natural        ║ - sector INT UNSIGNED DEFAULT NULL
║ 1      ║ 2     ║ NULL   ║ Greens         ║ - name VARCHAR(200) NOT NULL
║ 1      ║ 2     ║ 1      ║ Spring veggies ║ - PRIMARY KEY (family, class, sector)
║ 1      ║ 2     ║ 2      ║ Spring fruits  ║
║ 1      ║ 2     ║ 3      ║ Summer veggies ║
╚════════╩═══════╩════════╩════════════════╝

This table is about food categories. Their can be only one entry matching family+class+sector. The more primary key fields are populated, the more "specific" the category record is. A record having family+class+sector (in other words an actual sector category) would have 2 implicit parents: a/ A class record, having same family and class but sector set to NULL, b/ A family record, the top most category, having same family value but both class and sector set to NULL.

A sector record would have 0 children but 2 parents meaning $spring_fruit_object->getParents() would return a collection of food entities like [natural_hydrated_object, greens_hydrated_object] (eagerly).

Actually, I fear none of the existing associations mapping can handle this usecase automatically given the rules listed above. I will probably have to build custom queries this from the repository class.

How would you handle this scenario? Thank you.

Stphane
  • 3,368
  • 5
  • 32
  • 47

1 Answers1

0

(OFF: This would mean that only one food is allowed per family+class+sector. Are you sure that is what you want? Seems weird)

Should I add an auto-increment primary key, convert current Primary composed key into a UNIQ index and use simply repository with querybuilder?

IMO, composite primary keys should be avoided. They could be tempting to use, because they make sense, but:

  • Slower
  • Make you work slower by having to differ from your routine
  • Auto increment ID adds almost nothing extra
  • You will have to generate a combined id for some things, like routing.

Doctrine docs mention 3 use cases (https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/tutorials/composite-primary-keys.html#use-case-1-dynamic-attributes), all are valid, but I would only consider case 1, because that adds real value - you dont have to create attributes, simplifies the API.

Padam87
  • 1,013
  • 1
  • 6
  • 7
  • Come to think of it, not even case1 - the same can be done anyway. I was bamboozled by the EAV like implementation. – Padam87 Jul 15 '18 at 00:18
  • I have edited the question body to answer your 'off' note. I saw the reference documentation, sadly it seems that none of the associations is tailored to handle this usecase. – Stphane Jul 16 '18 at 17:31
  • "getParents() would return a collection of food entities" This can't be done throught associations. If you really need it you could create an onload listener to inject them into an unmapped field. But if you only need these on ocasion use the repository create a getParentsFor method. – Padam87 Jul 16 '18 at 17:36