Ebean OrderBy CASE WHEN

Question

When using Ebean finder, is it possible to provide a order by statement like the following:

ORDER BY 
   CASE
      WHEN a = 'FOO' THEN 1
      WHEN a = 'BAR' THEN 2
      ELSE 99
   END

Answer 1

It is possible to do such ordering. All we have to do is adding transient field and annotate it with @Formula with appropriate parameter.

So the model class should look like this:

@Entity
public class A extends Model {

    public A(Long aId, String aA) {
        id = aId;
        a = aA;
    }

    @Id
    public Long id;

    @Required
    public String a;

    @Transient
    @Formula(select = "(case when ${ta}.a = 'FOO' then 1 when a = 'BAR' THEN 2 else 99 end)")
    Integer index;

    public static Finder<Long,A> find = new Finder<Long,A>(Long.class, A.class);
}

This way we created virtual column 'index' and we can sort by it.

Here is exemplary test method:

@Test
public void aTest() {
    FakeApplication app = Helpers.fakeApplication(Helpers.inMemoryDatabase());
    Helpers.start(app);

    A a1 = new A(1L, "BAR");
    A a2 = new A(2L, "XYZ");
    A a3 = new A(3L, "FOO");
    A a4 = new A(4L, "AAA");
    A a5 = new A(5L, "FOO");
    A a6 = new A(6L, "BAR");

    a1.save();
    a2.save();
    a3.save();
    a4.save();
    a5.save();
    a6.save();

    List<A> list = Ebean.find(A.class).orderBy("index").findList(); 
    for(A fa: list) {
        System.out.println("fa.id:"+fa.id+" fa.a:"+fa.a);
    }

    System.out.println("-------------------------");

    List<A> list1 = A.find.orderBy("index").findList();     
    for(A fa: list1) {
        System.out.println("fa.id:"+fa.id+" fa.a:"+fa.a);
    }
}