JPA count related entities without joining them

Question

I have two entities:

@Entity
class X {
  @Id
  int id;
}

@Entity
class Y {
  @Id
  int id;

  @ManyToOne
  @JoinColumn(name = "x_id")
  X x;
}

I would like to count distinct values of x_id in y table. I've tried:

select count(distinct Y.x) from Y;

It works but in sql i get join to x table which is uneccesery:

select count(distinct x.id) from y, x where y.x_id = x.id;

This join is unnecessary and quite costly for me. Is there any way to avoid it without native query?

Answer 1

You may try with select count(distinct Y.x.id) from Y (T.x.id instead of Y.x). I am not sure, but intelligent JPA implementation should find out that only id is necessary and would not add the join.

Alternative is to add a int field to Y with a read-only mapping to x_id column:

@Entity
class Y {
  @Id
  int id;

  @ManyToOne
  @JoinColumn(name = "x_id")
  X x;

  @Column(name = "x_id", insertable = false, updatable = false, nullable = false)
  int xId;
}

And the your query would be simply select count(distinct Y.xId) from Y