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.

I think you have a wrong point in your SQL. Why you want to use a field to a join table and on the where you filter for null Ids? This query: "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". The where part make no sense. — endrigoantonini, Feb 16 '15 at 19:00

score 1 · Answer 1 · answered Jan 05 '15 at 23:57

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.

score 0 · Answer 2 · answered Feb 16 '15 at 19:04

I think this is what you are looking for:

    Finder<Long, User> finder = new Finder<Long, User>(Long.class, User.class);
    List<User> users = finder.fetch("mailLists").where().eq("mailLists.kind", "general").findList();

This piace of code will generate the following query:

SELECT U.id, U.email, M.user_id, M.kind
FROM users U
LEFT JOIN mail_list M ON U.id = M.user_id
WHERE M.kind = 'general';

I suggest you to use enum instead of static strings. This will be better to reference on your source code.

The unique part that I didn't understood on your question is the part that you use a field to join a table but on the where you filter for null values of that field.

How to make left join on two parameters in Ebean?

2 Answers2