How to link Ebean subquery to main query

Question

I need to find all users that have some money on their accounts and who have not been logging in for a long time. That's what I do:

Query<UserSessionRecord> subQuery =
                        Ebean.createQuery(UserSessionRecord.class)
                                .where().gt("loginDateTime", DateTime.now().minusMonths(1))
                                .eq("user", "user") // <-- here I need to refer somehow to the main query
                                .query();
//my main query
List<User> users = User.FIND.where().eq("account.balance", 0)
                        .notExists(subQuery)
                        .orderBy("id").findList();

This code doesn't work because the field user in UserSessionRecord is an entity User (not a String). How can I refer to the user in subquery?

Answer 1

You can give your user table a SQL alias. Then you can check equality using the raw()-method.

Query<UserSessionRecord> subQuery =
                    Ebean.createQuery(UserSessionRecord.class)
                            .where().gt("loginDateTime", DateTime.now().minusMonths(1))
                            .raw(mainuser.id=user.id)
                            .query();

List<User> users = User.FIND.query().alias("mainuser").where().eq("account.balance", 0)
                    .notExists(subQuery)
                    .orderBy("id").findList();