Where condition not working on related entity

I’m trying to apply the where condition on the related entity, but the result set contains all the related entity data. It appears like the filter is ignored. I have the following entities:

Entity Audit:

@Entity
@Table(name = "entity_audit")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@org.springframework.data.elasticsearch.annotations.Document(indexName = "entityaudit")
public class EntityAudit implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@org.springframework.data.elasticsearch.annotations.Field(type = FieldType.Keyword)
private Long id;

@NotNull
@Column(name = "entity_id", nullable = false)
private Long entityId;

@NotNull
@Column(name = "entity_class_name", nullable = false)
private String entityClassName;

@NotNull
@Column(name = "entity_name", nullable = false)
private String entityName;

@NotNull
@Enumerated(EnumType.STRING)
@Column(name = "action_type", nullable = false)
private EntityAuditType actionType;

@NotNull
@Column(name = "timestamp", nullable = false)
private Instant timestamp;

@NotNull
@Column(name = "user", nullable = false)
private String user;

@NotNull
@Column(name = "transaction_uuid", nullable = false)
private String transactionUuid;

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "entity_audit_id")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
private Set<EntityAuditUpdateData> entityAuditUpdateData = new HashSet<>();

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "entity_audit_id")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
private Set<EntityAuditStatus> entityAuditStatuses = new HashSet<>();

Getters and setters...

Entity Audit Status

@Entity
@Table(name = "entity_audit_status")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@org.springframework.data.elasticsearch.annotations.Document(indexName = "entityauditstatus")
public class EntityAuditStatus implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@org.springframework.data.elasticsearch.annotations.Field(type = FieldType.Keyword)
private Long id;

@NotNull
@Column(name = "user_login", nullable = false)
private String userLogin;

@NotNull
@Column(name = "jhi_read", nullable = false)
private Boolean read;

@ManyToOne
private EntityAudit entityAudit;

Getters and setters...

I’m trying to achieve this query:

@Query("select distinct entityAudit from EntityAudit entityAudit " +
    "join entityAudit.entityAuditStatuses entityAuditStatus " +
    "where entityAuditStatus.userLogin =:userLogin " +
    "order by entityAudit.timestamp desc")
Page<EntityAudit> retrieveAllByUserLogin(@Param(value = "userLogin") String userLogin, Pageable pageable);

But when I retrieve the data the EntityAuditStatuses are not filtered. I don’t understand where the problem is.

Answer

Note: I removed the date property from the minimum reproducible example.

Use left join fetch instead of left join to make sure the dependent entityAuditStatuses are fetched as part of the join query itself, and not as multiple queries after finding the entityAudit. And since the result needs to be paginated, an additional countQuery will need to be specified (without the fetch). Working Query –

@Query(value = "select entityAudit from EntityAudit entityAudit " +
            "left join fetch entityAudit.entityAuditStatuses entityAuditStatus " +
            "where entityAuditStatus.userLogin = :userLogin ",
       countQuery = "select entityAudit from EntityAudit entityAudit " +
            "left join entityAudit.entityAuditStatuses entityAuditStatus " +
            "where entityAuditStatus.userLogin = :userLogin ")

Without left join fetch, three queries are being generated – one which fetches the entityAuditId 1 (based on the userLogin 1) and then two more to fetch the entityAuditStatuses (from the entity_audit_status table only without the join) given the entityAuditId 1.

That is why, when you ask for userLogin = ‘1’ – you retrieve the EntityAudit 1 which brings with it – entityAuditStatus 1 – entityAuditStatus 3 (which has userLogin = ‘2’)

After adding left join fetch, there is only one query using join as per the defined entity relationships. So the results are correctly fetched.