Conditional @OnetoMany relationship in Ebean

Question

I have a parent entity which has a OneToMany relationship with a child entity.

I'd like to define a conditional relationship between these where child has an attribute column field with a value of 3. attribute is an integer column in child table like so in the model:

@Constraints.Required
@NotNull
protected Integer attribute;

Here is what I tried in the parent entity class:

@OneToMany(mappedBy = "parent")
@Where(clause = "attribute = 3")
List<Child> specificChildren;

However the generated query ignores the where clause I made, and gives me all children relating to that parent, not just the ones with attribute = 3. Changing the clause to ${ta}.attribute = 3 was also unsuccessful.

Here is the SQL generated query.getGeneratedSql():

select distinct on (t1.attribute, t0.id) t0.id, t1.attribute
from parent t0
left join child t1 on t1.parent_id = t0.id
order by t1.someotherattribute

The @Where attribute is being ignored completely, as no matter what I do with it, the generated SQL is not affected.

There is a io.ebean.annotation.Where class, so it seems like it should be able to work, but I haven't found any Ebean specific documentation on it (Ebean's docs do seem to be somewhat lacking). Most information I find about the @Where annotation relates to Hibernate. I did find the source code for it here though.

Is this supported in this way? Or is there another method this could be accomplished?

I'm currently using PostgreSQL 10.4, Ebean 10.4.4, Play 2.6.7, using Play's Ebean plugin 4.0.6. I tried updating to Play's Ebean 4.1.3 with no success. Not sure if the versions I am using are to blame or not.

More information found in a Google group here.

Answer 1

It works on my end, so let me please:

1. Firstly describe how it looks on my side
2. Then provide an assumption about the issue on your side
3. And then to ask some clarification questions (in case further help will be needed)

---

Working solution

I've used Ebean 10.4.4 and PostgreSQL 10 (works for MySQL 6.7 too).

1. Main part from the Employee class, it is a child entity:

   @ManyToOne
   @JoinColumn(name = "department_id")
   private Department department;
   
   protected int attribute;
   

2. Main part from the Department class, it is a parent entity:

   @OneToMany(mappedBy = "department")
   @Where(clause = "attribute = 3")
   private List<Employee> employees = new ArrayList<Employee>();
   

3. Find query

   Query<Department> query = Ebean.find(Department.class).fetch("employees")
           .where().eq("name", "DevOps").query();
   Department department = query.findUnique();
   String queryString = query.getGeneratedSql();
   System.out.println(queryString);
   

   Important part here is eagerly fetching "employees" field. Result output looks like this:

   select t0.id, t0.name, t1.id, t1.name, t1.attribute_test, t1.department_id
   from department t0
   left join employee t1 on t1.department_id = t0.id
   where attribute_test = 3 and t0.name = ?
   order by t0.id
   

---

Assumption

Main assumption (since I don't know how you've queried/searched the entity) is that you didn't fetched eagerly child entities and they were fetched using separate SQL.

E.g. in my example above if I will remove .fetch("employees") part and will do so:

Query<Department> query = Ebean.find(Department.class)
        .where().eq("name", "DevOps").query();
Department department = query.findUnique();
String queryString = query.getGeneratedSql();
System.out.println(queryString);
System.out.println(department); // uses this.employees in #toString() - so will lazily fetch child entities

I will have:

• The output below for the queryString:

  select t0.id, t0.name from department t0 where t0.name = ?
  

• Next output for the department (I have two employees in the department with attribute = 2 and one with attribute = 3):

  Department [
    id=1,
    name=java,
    employees=[{Employee [id=3, name=Batman, attribute=3, department=DevOps]}]
  ]
  

• And 2 SQL queries in the logs (utilized Logback here), one to find department, another to fetch employees (with @Where related logic):

  08:09:35.038 [main] DEBUG io.ebean.SQL - txn[1001] select t0.id, t0.name from department t0 where t0.name = ? ; --bind(java)
  08:09:35.064 [main] DEBUG io.ebean.SQL - txn[1002] select t0.department_id, t0.id, t0.name, t0.attribute_test, t0.department_id from employee t0 where attribute_test = 3 and (t0.department_id) in (?) ; --bind(1)
  

---

Clarification questions

In case things described above will not work, could you please provide next information:

1. Which version of PostgreSQL is using?

2. Is @ManyToOne used on the child entity side?

3. Could you please provide logs for the SQL queries initiated by your program?

   It will be enough to enable Logback for this like it is described here >>: just need to add dependencies and to utilize this >> configuration file.