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?

Looks like your question is more related to SQL than Ebean. Do you want to return all the book columns but distinct just by the title? Or do you need only the title column? Which database are you using? — marcospereira
– marcospereira, Commented Jan 20, 2016 at 3:11

Community · Accepted Answer · 2017-05-23 11:44:53Z

0

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:

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();
}

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jan 20, 2016 at 3:46

marcospereira

12.2k3 gold badges49 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1915863 Over a year ago

I know using plain sql we can do it very easily. And also here book table is in 'one to many' relation with the user. So even your your query will not work here if you do not use join in the query. Anyway ebean orm we can obtain one to many relation very easily. My concern is whether there is any way to distinct the title in ebean orm with annotation.

Collectives™ on Stack Overflow

Get distinct row form Mysql with Ebean on play framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related