How do I setup a Doctrine relation for a foreign key that could point to different tables, depending on context?

Question

I am creating an application, built with PHP, MySQL and Doctrine v1.2, that deals with ordered groupings of items, which can contain different types of items. For example, the group My Last Vacation can have images, video and textual notes. These items would be sortable so that they appear in the order that the end user specifies.

Each different type of item will have their own properties. An image may have a caption, a video may have a link to the youtube URL, etc. Items may exist in more than one grouping. E.g. the same image of a boat may exist in the groupings My Last Vacation and Cool Vehicles.

I am searching for the best way of implementing the above scenario with Doctrine relations and how those relations would be represented in a YAML schema file.

In my PHP code it would be great to be able to do something such as:

$group = new Group();  
$item1 = new Image();  
$item1->caption = 'my caption'  

$item2 = new Video();  
$item2->url = 'youtube.com/1234';  

$group->Items[0] = $item1;  
$group->Items[1] = $item2;  
$group->save();  

Is this possible? If not are there alternatives to achieving a similar result?

Answer 1

It is impossible as I know. Also I can't think of images, videos and texts as of single entity. They are surely different entities. The group "The last vacation" can be actually a group of subgroups "The last vacation - videos", "The last vacation - images", "The last vacation - texts". Is that convenient for you?

Answer 2

Your problem is not Doctrine but your database design. Foreign keys ALWAYS point from one table to exactly one other. Doctrine just maps this relation into your objects.

For your example I' d suggest, you make a table/class for "Entries" that can contain any kind of media. Just add fields for "TextBody", "ImageFile", "VideoFile", etc, etc... to that class.

Then these "Entries" need a foreign key to your "Groups" and you're done.

That way you can have Videos, that contain an additional text description and a preview image for example. All in one single object.

Maybe this approach needs some further normalization but that depends on th rest of your database design.

Answer 3

You can use supertype/subtype in DB design to avoid that problem. Create a supertype for images, video, notes and then link to the supertype.

Here are few links to several similar questions/answers with models:

• One
• Two
• Three
• Four

Answer 4

What you need is the inheritance feature of Doctrine. If you inherit both image and Video from the same base class you can use it in the way you want.

Example:

Asset:
  columns:
    group_id: { integer(20) }
  relations:
    Group: { class: Group, local: group_id, foreignAlias: Assets  }
  actAs:
    Timestampable: ~
Video:
  columns:
    url: { type: string(1024) }
  inheritance:
    extends: Asset
    type: column_aggregation
    keyField: type
    keyValue: video
Image: 
  columns:
    caption: { type: string(255) }
  inheritance:
    extends: Asset
    type: column_aggregation
    keyField: type
    keyValue: image
Group:
  ...

Thats it