Sorting by an association table column

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

I have the following tables:

Code:

CREATE TABLE parent ( parent_id NUMBER PRIMARY KEY );

CREATE TABLE child ( child_id NUMBER PRIMARY KEY );

CREATE TABLE parent_child (
  parent_id NUMBER NOT NULL,
  child_id NUMBER NOT NULL,
  sequence_num NUMBER NOT NULL,
  UNIQUE ( parent_id, sequence_num )
);

So it's a many-to-many, where child entities can be associated multiple times with the same parent (think: Scheduling system where a single task is performed twice, at different times, on the same day).

I've mapped the Parent and Child entities, and need to do the relation:

Code:

class Parent {
  // ...
  @ManyToMany( targetEntity=Child.class )
  @JoinTable( name="parent_child",
      joinColumns={ @JoinColumn( name="parent_id_id" ) },
      inverseJoinColumns={ @JoinColumn( name="child_id" ) } )
  private WhichCollection<Child> children = ?;
}

I'd like to order the children by the sequence_num in the join table. I noticed in the docs that EJB3 doesn't support this, "see Hibernate Annotations Extensions". The extensions section isn't clear. Do I need to use @Sort and whip up my own Comparator? If so, how do I get the value of "sequence_num" for that particular child in that particular parent? Or, worst case, do I need to break this up into a One-to-Many Parent-Assoc and Many-to-One Child-Assoc?

Also, which collection type should/could I use, if my Many-to-Many approach is the correct one?

Thanks!

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

Will @IndexColumn work for me, and sort things properly? What if a sequence number is missing? (ie: 1, 2, 3, 5, 6, 7)

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

And while we're at it, just a shot in the dark... what if the association table has, say, audit columns (create_user_id, create_time, modify_user_id, modify_time...) that can't be populated by triggers? Do I really need to split the association up at this point? Thanks :)

emmanuel · **Posted:** Fri Jul 07, 2006 9:14 am

@org.hibernate.annotations.OrderBy shoud work

if you add more properties to the assoc table, create an entity to represent the association table

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

Can I still use the @ManyToMany relationship? The reason I posted this in the EJB3 forum is because I'm using annotations. The documentation's a little less detailed than the main Hibernate documentation, so I'm unsure as how to use a mapped association entity with the @ManyToMany, but I'll keep reading.

Also, will the @OrderBy be able to reference table columns? Like if I were to put @OrderBy( "sequence_num" ), will it know to order by the column in the association table, rather than looking for a column or property in the parent/child tables?

emmanuel · **Posted:** Sun Jul 09, 2006 6:20 am

@ManyToMany works with @OrderBy yes.
@org.hibernate.annotations.OrderBy works on the association table column

when using an intermediate entity to map an association table you end up using

@OneToMany -> AssEntity <- @OneToMany

Annotations are very similar to the Hibernate core features. I'll try to work on hibernate annotations documentation but if you understand the hbm concepts , annotations are really similar

pegli · **Posted:** Fri Sep 01, 2006 1:01 pm

I had the same problem, and simply adding @IndexColumn to my collection mapping worked:

Code:

@ManyToMany
@org.hibernate.annotations.IndexColumn(name="display_order")
private List<Foo> foos;

The schema generated by "mvn hibernate3:schema-update" included the order column in the join table. I haven't tried discontinuous values in the display_order column yet, however.

atamur · **Joined:** Mon Sep 04, 2006 6:45 am **Posts:** 4

pegli wrote:

I had the same problem, and simply adding @IndexColumn to my collection mapping worked:

Code:

@ManyToMany
@org.hibernate.annotations.IndexColumn(name="display_order")
private List<Foo> foos;

The schema generated by "mvn hibernate3:schema-update" included the order column in the join table. I haven't tried discontinuous values in the display_order column yet, however.

I tried discontinuous values. It inserts nulls.
That's rather bad.

Moreover I still can't make hibernate to maintain my "display_order" column for me.
Ex:

Code:

Parent p = manager.find(Parent.class, pid);
Child c = new Child();
c.setTitle("abc");
p.getChildren().add(c);
c.setParent(p);
manager.persist(c);

This code doesn't run, because when manager.persist is called, property display_order of child isn't yet initialised. How should i write my code to delegate maintaincance of this display_order column in Child table o hibernate?

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

I do it manually.... can Hibernate handle this automatically?

emmanuel · **Posted:** Tue Sep 05, 2006 3:38 pm

silvaran wrote:

I do it manually.... can Hibernate handle this automatically?

I answered already in this very post :-)

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

emmanuel wrote:

silvaran wrote:

I do it manually.... can Hibernate handle this automatically?

I answered already in this very post :-)

OK maybe there's a misunderstanding... if I have the following two entities:

Code:

class Child {
  @ManyToOne @JoinColumn( name="parent_id" ) private Parent parent;
  @Basic @Column( name="sequence_num" ) private Integer sequence;
}

class Parent {
  @Id @Column( name="parent_id" ) private Long id;
  @OneToMany( mappedBy="parent" ) @IndexColumn( name="sequence_num", base=0 ) private List<Child> children = new LinkedList<Child>();
}

The question was... can hibernate populate "Child.sequence" automatically, rather than me having to shuffle the sequence numbers around when I insert a child?... a la:

Code:

class Parent {
  public void insertChild( int index, Child child ) {
    this.children.add( index, child );
    child.setParent( this );
    Iterator<Child> i = this.children.iterator();
    for ( int i = 0; i <= index; ++i ) { i.next(); }
    while ( i.hasNext() ) { i.next().setSequence( ++index ); }
  }
}

The above code probably (maybe) has bugs, I just whipped it up (I use something different), but hopefully it illustrates why it would be nice if Hibernate supported this.

Oh and a bonus question... if I have a unique constraint on (parent_id, sequence_num), and I change the order of children, can hibernate order things correctly to avoid violating the constraint? (so if I have a0, b1, c2, and I want to swap a and b's sequence, it would take two steps: a1, b1, c2; and a1, b0, c2; or is this unreasonable for all but the simplest cases?)... right now I'm using a deferred constraint (Oracle).

emmanuel · **Posted:** Wed Sep 06, 2006 12:14 am

@OneToMany(//no mappedBy

and on the other side @ManyToOne @JoinColumn(insertable=false, updatable=false) will do the job

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

Fantastic, will give it a shot, thanks...

silvaran · **Joined:** Wed Feb 15, 2006 9:09 pm **Posts:** 76

I tried something simpler, a tree structure in a table (also referred to http://opensource.atlassian.com/projects/hibernate/browse/ANN-7?page=all which didn't help):

Code:

TABLE menu_item (
  menu_item_id NUMBER PRIMARY KEY,
  parent_menu_item_id NUMBER,
  menu_sequence NUMBER NOT NULL
);

and the mapping...

Code:

@Entity
@Table( name="menu_item" )
public class MenuItem {

  @Id
  @Column( name="menu_item_id" )
  private Long id;

  @ManyToOne
  @JoinColumn( name="parent_menu_item_id" )
  private MenuItem parentMenuItem;

  @OneToMany( mappedBy="parentMenuItem" )
  @IndexColumn( name="menu_sequence", base=0, nullable=false )
  private List<MenuItem> childMenuItems = new LinkedList<MenuItem>();

}

So essentially, a menu, where the top-level menu items' parents are null. The "menu_sequence" index column (which isn't mapped to a property) doesn't get populated (even when adding children to an existing parent). I tried removing the "mappedBy" clause in the @OneToMany annotation as you suggested, but I get an error at configuration time (java.lang.NoSuchMethodError: org.hibernate.cfg.NamingStrategy.collectionTableName(Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;)Ljava/lang/String;), so it looks like it's trying to create that association table.

I also wasn't sure what putting "updatable=false, insertable=false" in the @JoinColumn would do. Would hibernate still populate parent_menu_item_id on insert, and update it as well (ignoring what I put in there), so all I need to do is put children in the childMenuItems list and be done with it?

emmanuel · **Posted:** Fri Sep 15, 2006 9:20 pm

You're explicitly not following what I told you, no surprise it does not work :-)

you must not have mappedBy