many-to-many relationship in doctrine

Question

I've got some tables on my system like these:

news
--id
--title
--content

video
--id
--title
--url

album
--id
--title

Now, I need to do a many-to-many relatioship with this tables, but in a flexible way. I created a table called 'links' with the below structure:

links
--parent_entity (example: news)
--parent_id   (example: 5)
--child_entity (exmaple: video)
--child_id (example: 2)

How can I map this using Doctrine?

Answer 1

Doctrine's documentation is quite good. I am not quite sure what you mean with "flexible" but here is the yaml configuration example for Many-To-Many relationships for Doctrine version 1.2.

---
User:
  columns:
    id:
      type: integer(4)
      autoincrement: true
      primary: true
    username:
      type: string(255)
    password:
      type: string(255)
  attributes:
    export: all
    validate: true

Group:
  tableName: group_table
  columns:
    id:
      type: integer(4)
      autoincrement: true
      primary: true
    name:
      type: string(255)
  relations:
    Users:
      foreignAlias: Groups
      class: User
      refClass: GroupUser

GroupUser:
  columns:
    group_id:
      type: integer(4)
      primary: true
    user_id:
      type: integer(4)
      primary: true
  relations:
    Group:
      foreignAlias: GroupUsers
    User:
      foreignAlias: GroupUsers

Answer 2

i like to do something like this:

Gadget:
  actAs: [Timestampable]
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    parent_entity:
      type: integer(4)
    parent_id:
      type: integer(4)
    child_entity:
      type: integer(4)
    child_id:
      type: integer(4)
  options:
    collate: utf8_unicode_ci
    charset: utf8

NewsVideo:
  inheritance:
    extends: Gadget
    type: column_aggregation
    keyField: parent_entity
    keyValue: 1
    keyField: child_entity
    keyValue: 2

thanks in advance!

Answer 3

This is not possible because you cannot do a proper join. The join in this case will depend on the value in parent_entity and child_entity (ie. each row may need to join to a different table). In addition, how will doctrine know which record type to hydrate (ie. because it is dependent on the entity type).

You may be able to pull something like this off (albeit it will be weird) by using the WITH clause on your joins. For example, in the setUp() method of your news model you could do:

$this->hasMany('Links as NewsVideoLinks', array('local' => 'id', 'foreign' => 'parent_id'));

And in the setup of your Links model:

$this->hasMany('Videos as videos', array('local' => 'child_id', 'foreign' => 'id'));

You would need to define all combinations of joins in the Links model. What I mean is, you would need to tell it that it has many news and albums as well using both the child_id and parent_id.

And then in your query, you would need to do something like:

$query = Doctrine_Query::create();
$query->from('News n');
$query->innerJoin("n.NewsVideoLinks as links WITH parent_entity = 'news'");
$query->innerJoin("links.Videos as vids WITH child_entity = 'videos'");
$results = $query->execute();

As you can see this is very cumbersome. I would highly recommend creating join tables for each relation. You would still get what you are looking for by joining out to each join table.

Answer 4

If I understand your database layout correctly "parent_entity" and "child_entity" represent the table you wish to link the id's to.

This is "technically" an incorrect database structure (see database normalization) since you can't map these columns to any other single table column.. Basically, you can't use your id columns now since it's ambiguous.

I think you have to re-think your database logic then it would make it easier to write the code afterwords.