Play Framework 2.2 - Ebean - ManyToMany OrderBy relationship creation date

Question

I have 2 models, a workout and an exercise as follows:

@Entity
public class Workout extends Model {

    @Id
    public Integer id;

    @ManyToMany(cascade = CascadeType.REMOVE)
    public List<Exercises> exercises;

}

@Entity
public class Exercise extends Model {

    @Id
    public Integer id;

    @ManyToMany
    public Workout workout;

}

When I load a workout and attempt to display it, I want the exercises to be displayed in the order that the relationship (between the workout and exercise) was created. However, the exercises are instead displayed in the order that the exercises were created. Here's a sample of the display (in case it helps):

<ul>
    @workout.exercises.map { exercise =>
    <li>
        @exercise.name    
    </li>
    }
</ul>

Any ideas on how I can achieve this? I've tried adding @OrderBy to the property definition, but this doesn't allow me to order by the relationship table fields, which would be ideal (with the addition of a created_date field on that table, as well).

Much appreciated!

Answer 1

In terms of SQL you need an order by clause to guarantee the order of the returned rows. That is the behaviour you are seeing of '... displayed in the order that the exercises were created.' ... is actual specific to your DB's convention (and not portable across DB's).

If you want SQL result ordering by 'order that the relationship (between the workout and exercise) was created' ... then what that strictly means is that you need a db column to store that on the intersection table and have the SQL order by that column.

Now, with @ManyToMany by default the generated intersection table does not have a 'When Created' column. You can either model the intersection table explicitly (change from @ManyToMany to use 2 @OneToMany etc) or manually define your intersection table with a 'When Created' column and DB triggers to populate that ... and then reference that db column in an order by clause.

Answer 2

There is no easy way as far as I can tell. Just create a getSortedExcercises() which returns the list sorted to use in the templates:

public List<Exercises> getSortedExcercises() {
    List<Exercises> l = new ArrayList(this. exercises);
    Collections.sort(l, new ExercisesComparator());
    return l;
}