Question about using orderby on the many side of a relation

[Gianluca Tessarolo]

Hi,

is it possible to extract a list (one side) objects sorted by a field on many side ?

If I have the user on one side and addresses on many side (ScModel is a mappedsuperclass with @Id, created_ad, updated_at fields):

=======

User.java

=======

package models;

import com.avaje.ebean.Page;

import java.util.ArrayList;

import java.util.List;

import javax.persistence.*;

@Entity

@Table(name = "users")

public class User extends ScModel {

  @Column(name = "name")

  public String name;

  @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)

  public List<Address> addresses = new ArrayList<Address>();

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

  public static Page<User> page(int page, int pageSize, String sortBy, String order, String filter) {

    return find.where().orderBy(sortBy + " " + order).fetch("addresses").findPagingList(pageSize).getPage(page);

  }

}

==========

Address.java

==========

package models;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.ManyToOne;

import javax.persistence.Table;

@Entity

@Table(name="addresses")

public class Address extends ScModel {

    @Required

    @Column(name="name")

    public String name;   

    @Required

    @ManyToOne

    public User user;

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

}

If I try to run:

Page<User> usersPage = User.page(0, 10, "addresses.name", "desc", "");

the resulting sql is:

select t0.id c0, t0.created_at c1, t0.updated_at c2, t0.name c3, t1.id c4, t1.created_at c5, t1.updated_at c6, t1.name c7, t1.user_id c8 

from users t0

left outer join addresses t1 on t1.user_id = t0.id  

order by t0.id, t1.name desc

and the user list has been sorted by user id, not by address name...

How I can get a list of users sorted by addresses title ?

Many thanks in advance...

[Gianluca Tessarolo]

Nobody ?

Maybe it's not possible ?

[Rob Bygrave]

You would have to use a @Formula or something like that. It can't be done in the simple fashion. 

[Gianluca Tessarolo]

Many thanks Rob you're right, It can't be done in the simple fashion, but most probably it can't be done at all...