how to map Self-referencing table ManyToMany relationship

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

how to map Self-referencing table ManyToMany relationship

reljicb
Hi!

I'm not very experienced with persistence, so if anybody of persistence experts have idea how to solve this, please assist.

I have database that models bi-directional graphs.
There is a Node table:

   Table node
   ==========
   id                INT
   name           VARCHAR
   nodeType     INT

Any Node can be linked to many other nodes. I use Link table as association table to link them.

   Table link
   ==========
   nodeId1     INT
   nodeId2     INT

When I generate persistence entities (NetBeans), I get a class member "linkedNodes" defined as following:

   @JoinTable(name = "link",
                   joinColumns = {@JoinColumn(name = "nodeId1", referencedColumnName = "id", nullable = false)},
                   verseJoinColumns = {@JoinColumn(name = "nodeId2", referencedColumnName = "id", nullable = false)})
   @ManyToMany
   private Collection<Node> linkedNodes;

The problem is that this JPA mapping behaves as if the graph is unidirectional - linkedNodes collection contains ONLY those nodes which are linked to this one in nodeId1->nodeId2 direction manner. In other words, table Link is queried for only those records where Id of the current node is in nodeId1 filed, and Ids of other (linked) nodes are in nodeId2 column, and not vice-verse.

This perfectly makes seanse when ManyToMany relationship is defined between two different tables.

But here I want to obtain all the nodes which are linked to the current one. In other words, when Id of current node is in EITHER nodeId1 OR nodeId2 column of the Link table.

Thanks in advance.
Your help is very much appreciated.

Bojan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to map Self-referencing table ManyToMany relationship

Oleg Mayevskiy
Am 12.08.2010 17:26, schrieb reljicb:
>     Table link
>     ==========
>     nodeId1     INT
>     nodeId2     INT
>
>    

with this table you can safe bidirectional graph ,e.g.
node1 -> node2 : nodeId1=node1;nodeId2=node2
node2 -> node1: nodeId1=node2;nodeId2=node1

if your graph is never unidirectional and you have only bidirectional
links, you can skip the second row.

if you now want alle nodes connected to node node1, you can make such a
query:
select nodeId2 from linkTable where nodeId1=node1
union
select nodeId1 from linkTable where nodeId2=node1

query optiomations can be done, but just an idea how you could manage
your problem

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to map Self-referencing table ManyToMany relationship

christopher delahunt
In reply to this post by reljicb
The relationship is unidirectional, otherwise there would be no way to
add a new reference to the list.   Without a direction being specified,
there would be no consistency to which 'side' of the relation table a
node gets added to, and it will lead to duplicates when being read in.  
And in most relationships, nodeId1->nodeId2 does not imply that nodeId2
references nodeId1.  Think of a person having a collection of children
(who are legally also persons).  You would not want a parent in its own
list of children.  

In JPA, when you want to access the relationships in the other direction
you need to create a mapping for it.  Since you wish to use the same
relationship table and just represent the NodeId2->nodeId1 relationship,
create a new collection and mark it with
@ManyToMany(mappedBy="linkedNodes").

Best Regards,
Chris




On 12/08/2010 11:26 AM, reljicb wrote:

> Hi!
>
> I'm not very experienced with persistence, so if anybody of persistence
> experts have idea how to solve this, please assist.
>
> I have database that models bi-directional graphs.
> There is a Node table:
>
>    Table node
>    ==========
>    id                INT
>    name           VARCHAR
>    nodeType     INT
>
> Any Node can be linked to many other nodes. I use Link table as association
> table to link them.
>
>    Table link
>    ==========
>    nodeId1     INT
>    nodeId2     INT
>
> When I generate persistence entities (NetBeans), I get a class member
> "linkedNodes" defined as following:
>
>    @JoinTable(name = "link",
>                    joinColumns = {@JoinColumn(name = "nodeId1",
> referencedColumnName = "id", nullable = false)},
>                    verseJoinColumns = {@JoinColumn(name = "nodeId2",
> referencedColumnName = "id", nullable = false)})
>    @ManyToMany
>    private Collection<Node> linkedNodes;
>
> The problem is that this JPA mapping behaves as if the graph is
> unidirectional - linkedNodes collection contains ONLY those nodes which are
> linked to this one in nodeId1->nodeId2 direction manner. In other words,
> table Link is queried for only those records where Id of the current node is
> in nodeId1 filed, and Ids of other (linked) nodes are in nodeId2 column, and
> not vice-verse.
>
> This perfectly makes seanse when ManyToMany relationship is defined between
> two different tables.
>
> But here I want to obtain all the nodes which are linked to the current one.
> In other words, when Id of current node is in EITHER nodeId1 OR nodeId2
> column of the Link table.
>
> Thanks in advance.
> Your help is very much appreciated.
>
> Bojan
>
>  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to map Self-referencing table ManyToMany relationship

reljicb
Thank you both a lot for the feedback.
Chris, your answer is a hit right into the target.

The way I decided to proceed with this is to create two members in my entity class (instead only one):

    @JoinTable(name = "link",
                    joinColumns = {@JoinColumn(name = "fromNode", referencedColumnName = "id", nullable = false)},
                    inverseJoinColumns = {@JoinColumn(name = "toNode", referencedColumnName = "id", nullable = false)})
    @ManyToMany
    private Collection<Node> outgoingLinks;

    @JoinTable(name = "link",
                joinColumns = {@JoinColumn(name = "toNode", referencedColumnName = "id", nullable = false)},
                inverseJoinColumns = {@JoinColumn(name = "fromNode", referencedColumnName = "id", nullable = false)}
              )
    @ManyToMany
    private Collection<Node> incomingLinks;


There will be getter functions to retrieve either only incoming/outgoing linked nodes, and one to retrieve all of them, by joining two collections into a single set.


Thanks once more!
Bojan



christopher delahunt wrote
The relationship is unidirectional, otherwise there would be no way to
add a new reference to the list.   Without a direction being specified,
there would be no consistency to which 'side' of the relation table a
node gets added to, and it will lead to duplicates when being read in.  
And in most relationships, nodeId1->nodeId2 does not imply that nodeId2
references nodeId1.  Think of a person having a collection of children
(who are legally also persons).  You would not want a parent in its own
list of children.  

In JPA, when you want to access the relationships in the other direction
you need to create a mapping for it.  Since you wish to use the same
relationship table and just represent the NodeId2->nodeId1 relationship,
create a new collection and mark it with
@ManyToMany(mappedBy="linkedNodes").

Best Regards,
Chris




On 12/08/2010 11:26 AM, reljicb wrote:
> Hi!
>
> I'm not very experienced with persistence, so if anybody of persistence
> experts have idea how to solve this, please assist.
>
> I have database that models bi-directional graphs.
> There is a Node table:
>
>    Table node
>    ==========
>    id                INT
>    name           VARCHAR
>    nodeType     INT
>
> Any Node can be linked to many other nodes. I use Link table as association
> table to link them.
>
>    Table link
>    ==========
>    nodeId1     INT
>    nodeId2     INT
>
> When I generate persistence entities (NetBeans), I get a class member
> "linkedNodes" defined as following:
>
>    @JoinTable(name = "link",
>                    joinColumns = {@JoinColumn(name = "nodeId1",
> referencedColumnName = "id", nullable = false)},
>                    verseJoinColumns = {@JoinColumn(name = "nodeId2",
> referencedColumnName = "id", nullable = false)})
>    @ManyToMany
>    private Collection<Node> linkedNodes;
>
> The problem is that this JPA mapping behaves as if the graph is
> unidirectional - linkedNodes collection contains ONLY those nodes which are
> linked to this one in nodeId1->nodeId2 direction manner. In other words,
> table Link is queried for only those records where Id of the current node is
> in nodeId1 filed, and Ids of other (linked) nodes are in nodeId2 column, and
> not vice-verse.
>
> This perfectly makes seanse when ManyToMany relationship is defined between
> two different tables.
>
> But here I want to obtain all the nodes which are linked to the current one.
> In other words, when Id of current node is in EITHER nodeId1 OR nodeId2
> column of the Link table.
>
> Thanks in advance.
> Your help is very much appreciated.
>
> Bojan
>
>  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to map Self-referencing table ManyToMany relationship

christopher delahunt
Hello Bojan,

This may cause you problems, as the both relationships will be writable, causing the potential for duplicates to be added.  For instance, if you add a new Node,  and add it to an existing Node's collection, that existing Node also needs to be added to the new one's collection.  This will cause writes from both sides, causing two entries in the relation table for the same relationship.  You can mark one side as read-only to prevent this, but the better way is to mark one side using the mappedby tag I mentioned in the previous email.  This is similar to making it read-only in that the owning relationship controls the relation table, but will allow changes to be merged into the cache instead of just being ignored. 

Best Regards,
Chris

On 13/08/2010 2:51 AM, reljicb wrote:
Thank you both a lot for the feedback.
Chris, your answer is a hit right into the target. 

The way I decided to proceed with this is to create two members in my entity
class (instead only one):

    @JoinTable(name = "link", 
                    joinColumns = {@JoinColumn(name = "fromNode",
referencedColumnName = "id", nullable = false)}, 
                    inverseJoinColumns = {@JoinColumn(name = "toNode",
referencedColumnName = "id", nullable = false)})
    @ManyToMany
    private Collection<Node> outgoingLinks;

    @JoinTable(name = "link", 
                joinColumns = {@JoinColumn(name = "toNode",
referencedColumnName = "id", nullable = false)},
                inverseJoinColumns = {@JoinColumn(name = "fromNode",
referencedColumnName = "id", nullable = false)}
              )
    @ManyToMany
    private Collection<Node> incomingLinks;


There will be getter functions to retrieve either only incoming/outgoing,
and one to retrieve all of them, by joining two collections into a single
set.


Thanks once more!
Bojan




christopher delahunt wrote:
  
The relationship is unidirectional, otherwise there would be no way to 
add a new reference to the list.   Without a direction being specified, 
there would be no consistency to which 'side' of the relation table a 
node gets added to, and it will lead to duplicates when being read in.  
And in most relationships, nodeId1->nodeId2 does not imply that nodeId2 
references nodeId1.  Think of a person having a collection of children 
(who are legally also persons).  You would not want a parent in its own 
list of children.  

In JPA, when you want to access the relationships in the other direction 
you need to create a mapping for it.  Since you wish to use the same 
relationship table and just represent the NodeId2->nodeId1 relationship, 
create a new collection and mark it with 
@ManyToMany(mappedBy="linkedNodes").

Best Regards,
Chris




On 12/08/2010 11:26 AM, reljicb wrote:
    
Hi!

I'm not very experienced with persistence, so if anybody of persistence
experts have idea how to solve this, please assist. 

I have database that models bi-directional graphs.
There is a Node table:

   Table node
   ==========
   id                INT
   name           VARCHAR
   nodeType     INT

Any Node can be linked to many other nodes. I use Link table as
association
table to link them. 

   Table link
   ==========
   nodeId1     INT
   nodeId2     INT

When I generate persistence entities (NetBeans), I get a class member
"linkedNodes" defined as following:

   @JoinTable(name = "link", 
                   joinColumns = {@JoinColumn(name = "nodeId1",
referencedColumnName = "id", nullable = false)}, 
                   verseJoinColumns = {@JoinColumn(name = "nodeId2",
referencedColumnName = "id", nullable = false)})
   @ManyToMany
   private Collection<Node> linkedNodes;

The problem is that this JPA mapping behaves as if the graph is
unidirectional - linkedNodes collection contains ONLY those nodes which
are
linked to this one in nodeId1->nodeId2 direction manner. In other words,
table Link is queried for only those records where Id of the current node
is
in nodeId1 filed, and Ids of other (linked) nodes are in nodeId2 column,
and
not vice-verse.

This perfectly makes seanse when ManyToMany relationship is defined
between
two different tables.

But here I want to obtain all the nodes which are linked to the current
one.
In other words, when Id of current node is in EITHER nodeId1 OR nodeId2
column of the Link table.

Thanks in advance. 
Your help is very much appreciated. 

Bojan

  
      
    

  
Loading...