Where condition not working on related entity

Question

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.

Is it a bidirectional relationship ? If so, who is the relationship owner ? Try adding `@OneToMany(mappedBy="entityAudit")` in `EntityAudit` and `@ManyToOne` with `JoinColumn` details in `EntityAuditStatus`. Do also check on the type of join whether you want all entities regardless of an audit status or only the ones with an audit status. — S B, Feb 12 '20 at 11:10
Adding the mappedBy and removing the JoinColumn from EntityAudit, adding it into the EntityAuditStatus makes that when I save the EntityAudit object with EntityAuditStatuses in it, EntityAuditStatus records do not have the EntityAudit id — ChainlessWEB, Feb 12 '20 at 13:13
But does it add entityAuditStatus related IDs in EntityStatus related table ? — S B, Feb 12 '20 at 13:17
It should be the opposite. The EntityAudit ID should be added in the EntityAuditStatus table as fk. Without changing JoinColumn and mappedBy it works — ChainlessWEB, Feb 12 '20 at 13:20
Hibernate query: select distinct entityaudi0_.id as id1_5_, entityaudi0_.action_type as act ion_t2_5_, entityaudi0_.entity_class_name as entity_c3_5_, entityaudi0_.entity_id as entity_i4_5_, entityaudi0_.entity_name as entity_n5_5_, entityaudi0_.timestamp as tim estam6_5_, entityaudi0_.transaction_uuid as transact7_5_, entityaudi0_.user as user8_ 5_ from entity_audit entityaudi0_ inner join entity_audit_status entityaudi1_ on enti tyaudi0_.id=entityaudi1_.entity_audit_id where entityaudi1_.user_login=? order by ent ityaudi0_.timestamp desc limit ? — ChainlessWEB, Feb 12 '20 at 13:21
Ok, so what is the result of you revert the changes (your original code) and if you use inner join instead of left join in your JPQL (as you are using inner join in the SQL) — S B, Feb 12 '20 at 13:24
Using the data setup per the following queries - insert into entity_audit (id) values (1), (2); insert into entity_audit_status (id, user_login, entity_audit_id) values (1, '1', 1), (2, '1', 2), (3, '2', 1); So, entityAuditId 1 (entityAuditStatusId = 1) and 2 (entityAuditStatusId = 2) are for userLogin 1 and entityAuditId 1 is for userLogin 2 (entityAuditStatusId = 3) `@Query("select entityAudit from EntityAudit entityAudit " + "join entityAudit.entityAuditStatuses entityAuditStatus " + "where entityAuditStatus.userLogin = :userLogin " )` — S B, Feb 12 '20 at 14:01
I changed only from left to inner join as I need only EntityAudit with an EntityAuditStatus that matches the where condition. That was a mistake while writing the question. However, I've updated the code — ChainlessWEB, Feb 12 '20 at 14:01
Ok using your example the result is that: - I ask for userLogin = '1' - I retrieve the EntityAudit 1 which brings with it - entityAuditStatus 1 - entityAuditStatus 3 (which has userLogin = '2') — ChainlessWEB, Feb 12 '20 at 14:09

score 0 · Accepted Answer · answered Feb 12 '20 at 15:07

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.

I've just reached the same conclusion. Already tested and fetch + countQuery (because is paginated) fixed the problem. I'll mark this as solution. Thank you very much! — ChainlessWEB, Feb 12 '20 at 16:47

Where condition not working on related entity

1 Answers1