19

I've got two tables and a join table: 'staff', 'classification' and 'staff_classification'. In the join table I've got an extra boolean field: 'showclassification'. My annotation is as follows:

 /**
 * @ManyToMany(targetEntity="Staff", inversedBy="classifications")
 * @JoinTable(name="staff_classifications",
 *  joinColumns={@JoinColumn(name="staffid", referencedColumnName="id")},
 *  inverseJoinColumns={@JoinColumn(name="classificationid", referencedColumnName="id", unique=true)});
 */
  1. How do I add the extra field 'showclassifications' to the join table?
  2. How do I reference the field via DQL? E.g. What query would get all of a staff's classifications that are allowed to be shown?
  3. Do I place the above annotation in one class and a @ManyToMany annotation with no @joinTable in the other? E.g. @ManyToMany (targetEntity="Classification")?
waigani
  • 3,570
  • 5
  • 46
  • 71
  • Here you can find a great article about the approach to follow in this case: http://future500.nl/articles/2013/09/doctrine-2-how-to-handle-join-tables-with-extra-columns/ – Ema.jar Nov 03 '14 at 16:54

1 Answers1

32

You want an entity that describes the relationship (StaffClassifications), which has OneToMany relationships with both staff and classifications.

ManyToMany doesn't allow you have any "extra" properties, because the join table is not an entity, and thus can't have any properties.

timdev
  • 61,857
  • 6
  • 82
  • 92
  • 3
    +1 It's the logical OO way to do things. This exact question gets asked a lot on the mailing list, and it's what the Doctrine team suggests as well. – Bryan M. Dec 14 '10 at 19:14
  • awesome. Thanks, I'll follow that pattern and see how I go. Cheers. – waigani Dec 14 '10 at 21:39
  • 1
    Using the OO pattern, what is the answer to question 2 above? What would the DQL look like? – waigani Dec 14 '10 at 23:10
  • Somethlink like `select s, sc, c from Staff s join StaffClassificaiton sc join Classification c ...` – timdev Dec 15 '10 at 05:39