Mapping Many to Many relation with attribute.

36876 views
Skip to first unread message

FMaz

unread,
Nov 1, 2011, 6:25:10 PM11/1/11
to doctrine-user, g.co...@gmail.com
Hi everyone,

We're having a lot of tables with Many to Many relation, and extra
columns. An quick easy example could be:

table person:
- id
- name

table email:
- id
- email

table contact:
- person_id
- email_id
- type

===========
- "move 'type' in the email table":
I've used an easy to understand example.
type is not in the email table because other table link to email, but
without type, or with different type, or with differents columns,
anyway, the example is a simple one ;) )
===========

So basically, we're scrambling 2 things into one:
- the relation.
- a specification of the relation.

From a relational perspective, it's a great solution as it avoid us to
create a contact table, and 2 extra relations tables ( person to
contact, contact to email, and possibly person to email ). Not to
mention that this kind of solution would increase the number of JOIN
required in the application, and could/would have a negative impact on
performances.

So have you encoutered similar problems; if so what were the good
solutions that worked for you ?
Thanks for sharing your experience.

Ps.: We're using Doctrine 2.

Menno Holtkamp

unread,
Nov 2, 2011, 8:08:02 AM11/2/11
to doctri...@googlegroups.com

What is exactly "the problem" you have? Your approach seems right, you are worried about performance,or ... ?

Can you be more specific?

Cheers

--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Francois Mazerolle

unread,
Nov 2, 2011, 9:28:03 AM11/2/11
to doctrine-user
>> What is exactly "the problem" you have?

Hehe, sorry if I wasn't limpid;

The problem I have is that I don't know how to configure the Doctrine2
mapping to handle attributes on relations.

A stackoverflow topic really explain well the problem, but no real
good solutions has been given so far:
http://stackoverflow.com/questions/3542243/doctrine2-best-way-to-handle-many-to-many-with-extra-columns-in-reference-table


The only solution I've found so far was to try to implement a 3 way
many to many relation (with all the relations tables it need), and
consider the attribute as an entity, but that would be a real pain to
manage.

Jasper N. Brouwer

unread,
Nov 2, 2011, 9:44:17 AM11/2/11
to doctri...@googlegroups.com
It sounds to me that you've encountered a very common problem: A many-to-many relation where you want additional columns in the join-table (or cross-reference-table).

The most common solution to this problem is to create an entity for the relation itself and use one-to-may / many-to-one relations. In your example you'll get:

entity: Person
- property: $id
- property: $name
- one-to-many relation: $contact
- shortcut-method: getEmails()

entity: Contact
- many-to-one relation: $person
- many-to-one relation: $email
- property: additional a
- property: additional b
- etc..

entity: Email
- property: $id
- property: $address
- one-to-many relation: $contact
- shortcut-method: getPersons()

So your cross-referency-table becomes an entity itself and you can put in any additional columns you want.
For convenience you can create the shortcut-methods so you don't have to do:
foreach( $person->getContacts() as $contact ) {
$contact->getEmail();
}
every time you want to retrieve the Emails from a Person (or visa versa).


PS: You can find the following note in the Doctrine 2 docs:
http://www.doctrine-project.org/docs/orm/2.1/en/reference/association-mapping.html#many-to-many-unidirectional
> Why are many-to-many associations less common? Because frequently you want to associate additional attributes with an association, in which case you introduce an association class. Consequently, the direct many-to-many association disappears and is replaced by one-to-many/many-to-one associations between the 3 participating classes.


--
Jasper N. Brouwer

Jasper N. Brouwer

unread,
Nov 2, 2011, 9:53:41 AM11/2/11
to doctri...@googlegroups.com
Just to be clear: This is the solution you are currently working with.

If you are actually getting performance problems:
- Check if retrieving a complete Person (with a single query with joins) is the right way to go. When you don't use joins, but Doctrines default behavior of lazy loading, multiple queries (no joins) will be used. This could give you a performance gain, but it also could not. This depends on you situation, you should benchmark it.
- Check your database setup. Do you have indexes in the right places? Adding an index where you need one will give you a huge performance gain ;)

--
Jasper N. Brouwer


On 02-11-2011, at 14:28, Francois Mazerolle wrote:

Francois Mazerolle

unread,
Nov 2, 2011, 9:59:46 AM11/2/11
to doctrine-user
So basically, the solution is to consider the relation as an entity,
and think that the many to many relation that was linking Person to
Email is now a Many to one relation between Person and Contact, and an
additional one to many relation between Contact and Email.

No extra tables are required, but we must code an extra entity for
every relation we will need to have.

So far it looks like a good solution !
Thanks for the tips !

Jasper N. Brouwer

unread,
Nov 2, 2011, 10:14:26 AM11/2/11
to doctri...@googlegroups.com
That's correct :)

Real many-to-many (or cross-references) are very rare. By definition a cross-reference table only contains 2 columns (2 foreign keys (that together form the primary key) to be exact). And usually you'll need more columns, which means it's no longer just a cross-reference.

Good luck!

--
Jasper N. Brouwer

quba

unread,
Nov 3, 2011, 5:05:32 PM11/3/11
to doctrine-user
Ok, so now with a live example: manytomany relation with an
association class containing extra fields. So really an onetomany -
manytoone. Here's the code:

class UserPaper
{
/**
* @var integer $id
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;

/**
* @ORM\ManyToOne(targetEntity="Zpi\UserBundle\Entity\User")
*/
private $user;

/**
* @ORM\ManyToOne(targetEntity="Zpi\PaperBundle\Entity\Paper")
*/
private $paper;

/**
* @var integer $type
*
* @ORM\Column(name="type", type="smallint")
*/
private $type;

public function __construct(User $user, Paper $paper, $type = 0)
{
$this->user = $user;
$this->paper = $paper;
$this->type = $type;
}
...
and so on
}

I've created a proxy method inside in order to easy get paper title
from user class:

public function getTitle()
{
return $this->getPaper()->getTitle();
}

Sure, everything's ok but..

While trying to get all user's papers titlel i see such queries:
SELECT t0.id AS id1, t0.type AS type2, t0.user_id AS user_id3,
t0.paper_id AS paper_id4 FROM users_papers t0 WHERE t0.user_id = ?
Parameters: [1]
Time: 0.52 ms
SELECT t0.id AS id1, t0.title AS title2, t0.abstract AS abstract3,
t0.owner AS owner4 FROM papers t0 WHERE t0.id = ?
Parameters: ['1']
Time: 0.46 ms
SELECT t0.id AS id1, t0.title AS title2, t0.abstract AS abstract3,
t0.owner AS owner4 FROM papers t0 WHERE t0.id = ?
Parameters: ['5']
Time: 0.44 ms
SELECT t0.id AS id1, t0.title AS title2, t0.abstract AS abstract3,
t0.owner AS owner4 FROM papers t0 WHERE t0.id = ?
Parameters: ['6']
Time: 0.46 ms
SELECT t0.id AS id1, t0.title AS title2, t0.abstract AS abstract3,
t0.owner AS owner4 FROM papers t0 WHERE t0.id = ?
Parameters: ['7']
Time: 0.46 ms

One more record means one more query. Why isn't doctrine getting all
this data using a join statement? What is the best method to solve it?
Proxy method is a good thing, when you need to get a single data row.

Marco Pivetta

unread,
Nov 3, 2011, 6:59:17 PM11/3/11
to doctri...@googlegroups.com
You could use EAGER loading or fetch join would allow you to reduce the number of queries :)
Marco Pivetta
http://twitter.com/Ocramius
http://marco-pivetta.com



quba

unread,
Nov 3, 2011, 7:37:31 PM11/3/11
to doctrine-user
Solved with:
$papers = $this->getDoctrine()->getEntityManager()
->createQuery('SELECT p, up FROM
ZpiPaperBundle:UserPaper up INNER JOIN up.paper p WHERE up.user
= :uid')
->setParameter('uid', $user->getId())
->execute();

But I would like to have a function $user->getPapers() and I don't
know how and where to define it. Default function is getting data only
from association class (users_papers).
Tried to define repository class for user but it works only using
entitymanager.

On 3 Lis, 23:59, Marco Pivetta <ocram...@gmail.com> wrote:
> You could use EAGER loading or fetch join would allow you to reduce the
> number of queries :)
> Marco Pivettahttp://twitter.com/Ocramiushttp://marco-pivetta.com

Menno Holtkamp

unread,
Nov 3, 2011, 10:53:46 PM11/3/11
to doctri...@googlegroups.com

Such a helper function ($user->getPapers()) is the responsibility of the developer to implement using the association class you have introduced.

Yes, this takes some iterating over your UserPaper Entities and assembling a new collection for your papers, but there is currently no way that Doctrine can detect that your UserPaper Entity is an association class (would be a nice feature though, as I think of it ;) ) and generate the required proxy functions.

Cheers

Elio Jean-francois

unread,
Nov 21, 2012, 7:09:09 AM11/21/12
to doctri...@googlegroups.com, g.co...@gmail.com, fma...@gmail.com
Hi everyone,

I think I understand your solution, but I have an extra question about one case.

I'll take the same exemple to ask my question, so we've got :

table person: 
- id 
- name 

table email: 
- id 
- email 

table contact: 
- person_id 
- email_id 
- type 

Imagine you want to also allowed multiple contact and not only one like the solution of Jasper, like 

table contact: 
- id
- person_id 
- email_id 
- type 

Is it possible to do something like that and how:

entity: Contact
- property: $id

- many-to-one relation: $person
- many-to-one relation: $email
- property: additional a
- property: additional b
- etc..

Thanks

Juan Manuel

unread,
Jun 6, 2014, 10:32:18 AM6/6/14
to doctri...@googlegroups.com, g.co...@gmail.com, fma...@gmail.com
Hi All,

   Sorry to bump this aold question up. But I wonder if, for specific extra collumns like Doctrine1.x added by behaviours, (e.g. createdAt, createdBy) there is any way to add them by annotated configs in the ManyToMany..

 Thanks! 

Jasper N. Brouwer

unread,
Jun 11, 2014, 10:32:44 AM6/11/14
to doctri...@googlegroups.com
If you mean you want the columns `created_at`, `created_by`, etc in the join-table: no.
As discussed, Doctrine 2 doesn't support this. You'll have to make a join-entity and create associations as: `EntityA` <- OneToMany -> `JoinEntity` <- ManyToOne -> `EntityB`. Then you can add the properties `createdAt`, `createdBy`, etc in `JoinEntity`.

As for behaviors, Doctrine Excetions [1] can help.

[1]: https://github.com/Atlantic18/DoctrineExtensions

--
Jasper N. Brouwer
(@jaspernbrouwer)
Reply all
Reply to author
Forward
0 new messages