MySQL Doctrine YAML

Question

What I want is get the product and the product should be something like this:

{
  idProduct: 1,
  mainCategory: 1, // One of the categories is the main one.
  categories: [1, 2, 3],
  name: "John" //and so on.. the important thing is mainCategory...
}

Im using Doctrine (at work) with YAML, and I know I could use something like this:

Product:
  type: entity
  manyToMany:
    groups:
      targetEntity: Category
      joinTable:
        name: products_categories
        joinColumns:
          product_id:
            referencedColumnName: id
        inverseJoinColumns:
          category_id:
            referencedColumnName: id

That would create something like this three tables (use your imagination xD):

**Product**
id

**Category**
id

**Category_Product**
id_product
id_category

But I need mainCategory in the product too:

**Product**
id
main_category #this is important and my problem.

What can I do to achieve this... I put an object example in json because is easy to understand... but Im using PHP at work, with Doctrine.. I guess Doctrine2, Im not sure about that.

At work I have in production category and product already working but in a one to one relationship... today one product can have only one category, my issue is change that, because one product can have several categories but one is the main one.

Thanks in advance!!!

Edit: Someone at work suggest me, a third entitie, but Im not sure about that.

Answer 1

Maybe you can think outside of the box a little and define mainCategory as just an ID. This ID can be used to look up categories. Instead of calling mainCategory via regular doctrine, write a custom function that would get the mainCategory object from this mainCategory ID.

Yes, it is manual work, but doctrine does not natively support your scenario. The most important thing I would worry about is making sure mainCategory and Categories stay in sync such that you can never have a mainCategory that is not in category. You can do this easily though if you always check that mainCategory is in Categories on save.