Get distinct row form Mysql with Ebean on play framework

Question

I'm new to Ebean's world for play framework.

I have basically two classes, User and Book.

A user have several books. Also a user may have books with same title multiple time what ever the author or edition is. What i need is to link the books with user where same title of book will come only once whatever the other proprieties of the book is. That means retrieving the book list distinct on book's title.

Here it is what I've done so far

User Class

@Entity
public class User extends Model{
    @Id
    public int id;  
    public String name; 
    @ManyToMany(targetEntity = Book.class,cascade=CascadeType.ALL)
    public List<Book> book;
}

Book Class

@Entity
public class Book extends Model{
    @Id
    public int id;
    @Column(unique=true)
    public String name;
    public String author;
    public int edition;
    public int user_id;
}

But the multiple books having same title are not distinctly filtered. How to put the annotation correctly here so that I can only get one row for same title of book?

Answer 1

As I said, your problem is also related to SQL instead of just Ebean. First, here are some discussions about how to select multiple columns but distincting by a single one:

1. mySQL select one column DISTINCT, with corresponding other columns
2. How to select distinct value from one column only
3. MySQL - SELECT all columns WHERE one column is DISTINCT
4. SQL/mysql - Select distinct/UNIQUE but return all columns?

Then, you will need to execute the correct SQL query (based on the questions above) using Ebean:

public static List<Book> findDistinctBooks(String author) {
    String sql = "select b.id, b.name, b.author, b.edition from book b where author = :author group by name";
    RawSql rawSql = RawSqlBuilder.parse(sql)
            .columnMapping("b.id", "id")
            .columnMapping("b.name", "name")
            .columnMapping("b.author", "author")
            .columnMapping("b.edition", "edition")
            .create();

    return Ebean.createQuery(Book.class)
            .setRawSql(rawSql)
            .setParameter("author", author)
            .findList();
}