I have two tables, which can be thought of one being a list of parts in a product, and another being the internationalisation table for the full name of those parts. I'm trying to specify a relationship in either Doctrine class code or YAML, but I can't see how to do so on more than one key.
For example, from Product to Part is easy, as the tables look like:
PRODUCT:
- product_id
- product_name
- ...
PART:
- part_id
- product_id
- part_type_id
- part_short_code
- ...
Since I can tell YAML / Doctrine that product_id is a foreign key from PART to PRODUCT, the mapping's trivial; product->part Just Works.
The difficulty comes when I add the PART_TRANSLATION table:
PART_TRANSLATION:
- part_type_id
- part_short_code
- language_id
- internationalised_name
- ...
How do I tell doctrine that, for the relationship to be valid, both part_type_id and part_short_code must match in PART and PART_TRANSLATION, and language_id must be 1? Is it even possible?
NB: this is a third party database whose structure I can't change. DB is MySQL if that makes any difference. I'm tempted to create a MySQL VIEW that specifies these relations for the moment, but I'd like to know if I could instead do it with Doctrine directly.
Thanks in advance for any hints!