How to make left join on two parameters in Ebean?

Question

I have two tables: "users" and "mail_list" with corresponding classes.
These tables are connected with the help of foreign key user_id (in mail_list table) that references id (in users table). Users can have records of two kinds in mail_list table - 'general' or/and 'admin'. If user has a record in mail_list table, this means that he doesn't want to recieve mails of corresponding kind.
I'd like to find all users who want to recieve mails of general kind. I'm sure that the right SQL query looks like this:

SELECT U.id, U.email, M.user_id, M.kind
FROM users U
LEFT JOIN mail_list M
ON (U.id = M.user_id AND M.kind = 'general')
WHERE M.user_id IS NULL

But unfortunately I'm not so good with Ebean. Could you, please, help me to write such a Ebean query if it is possible? I'd like to avoid using Raw SQL.

Here, also, some code of my classes is:

@Entity
@Table(name = "users")
public class User {
    @Id
    public Long id;

    public String email;

    @OneToMany(mappedBy = "user")
        public List<MailList> mailLists;
    }

    @Entity
    @Table(name = "mail_list")
    public class MailList {
        @Id
        public Long id;

    /**
     * Kind of mail list
     */
    public String kind;
    public static String GENERAL = "general";
    public static String ADMIN = "admin";

    @ManyToOne
    public User user;
}

I use PlayFramework 2.2.3.

Answer 1

My solution to your problem is:

List<MailList> mailList = MailList.find.where().like("kind", "general").findList();     
Set<User> userSet = new HashSet<User>();
for(MailList mail:mailList)
    userSet.add(mail.user);

It finds mailing lists that fulfill search criteria. Then it creates set of users.