Can I use @Where annotation along with @ManytoOne association?

Question

I am not an expert in Spring, JPA, Hibernate or MySql. However I am using all for a web service supporting RESTful calls. I am building a store management app backend with Spring. My entities at this point of time are StoreModel, StoreUserModel, StoreUserRoleModel and StoreUserAuthModel.

I have setup bidirectional relationships(OneToMany and ManyToOne) between StoreModel - StoreUserAuthModel, StoreUserMode - StoreUserAuthModel and StoreUserRoleMode - StoreUserAuthModel.

I dont want the foreign key constraint though there are foreign key fields storeid, roleid and userid in StoreUserAuthModel.

Now All the four tables have isdeleted column to implement soft delete. I am lazy fetching the associations. However I dont want the softdeleted values whenever i query the associations.

I would like to know if I can use @Where annotation along with the @ManyToOne annotation in the StoreUserAuthModel entity?

The issue is different from How to use @Where in Hibernate because my problem is with ManyToOne annotation whereas I have used the where annotation with OneToMany

@Entity
@Table(name = "store")
public class StoreModel {

    @NotBlank
    private String name;

    @NotBlank
    private String address;

    @NotBlank
    private String city;

    @NotBlank
    private String phone;


    @JsonIgnore
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "storeid", foreignKey = @ForeignKey(name="none", value = ConstraintMode.NO_CONSTRAINT ))
    @Where(clause="isdeleted = 0")
    private List<StoreUserAuthModel> authList = new ArrayList<StoreUserAuthModel>();


    ...
}

@Entity
@Table(name = "storerole")
public class StoreRoleModel {

    @NotBlank
    private String name;

    @NotBlank
    private Integer rolehierarchy;

    @JsonIgnore
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "roleid", foreignKey = @ForeignKey(name="none", value = ConstraintMode.NO_CONSTRAINT ))
    @Where(clause="isdeleted = 0")
    private List<StoreUserAuthModel> authList = new ArrayList<StoreUserAuthModel>();

    ...


}

@Entity
@Table(name = "storeuser")
public class StoreUserModel{

    @NotBlank
    @Column(unique = true)
    private String username;

    @Email
    @Column(unique = true)
    private String useremail;

    @JsonIgnore
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "userid", foreignKey = @ForeignKey(name="none", value = ConstraintMode.NO_CONSTRAINT ))
    @Where(clause="isdeleted = 0")
    List<StoreUserAuthModel> userAuthList = new ArrayList<StoreUserAuthModel>();

    ...

}

@Entity
@Table(name = "storeuserauth", 
        uniqueConstraints = @UniqueConstraint(columnNames = {"storeid", "roleid", "userid"}))
public class StoreUserAuthModel {

    @NotNull
    Long storeid;

    @NotNull
    Long roleid;

    @NotNull
    Long userid;

    // Using @where to filter out the soft deleted storeuser
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="userid", foreignKey = @ForeignKey(name="none",  value = ConstraintMode.NO_CONSTRAINT ),insertable = false, updatable = false )
    @Where(clause="isdeleted = 0")
    private StoreUserModel storeuser;

    // Using @where to filter out the soft deleted store
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="storeid", foreignKey = @ForeignKey(name="none",  value = ConstraintMode.NO_CONSTRAINT ),insertable = false, updatable = false )
    @Where(clause="isdeleted = 0")
    private StoreModel store;

    // Using @where to filter out the soft deleted role
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="roleid", foreignKey = @ForeignKey(name="none",  value = ConstraintMode.NO_CONSTRAINT ),insertable = false, updatable = false )
    @Where(clause="isdeleted = 0")
    private StoreRoleModel role;

    ...


}


// In the controller, Following code shows how I plan to use

    Optional<StoreUserModel> aUser = storeUserRepository.findByUseremailAndIsdeleted(zUserMail), 0);
    if(aUser.isPresent()) {
        // The user was found!!!

        // Testing...
        // Getting the User Auth List (that will filter out the soft deleted auths)
        List<StoreUserAuthModel> authList = aUser.get().getUserAuthList();
        for(StoreUserAuthModel auth :authList) {
            StoreModel store = auth.getStore();
            // here both soft deleted store as well as normal stores are shown.
            // ie where clause  on  store relation is not working!!

            logger.debug("Store is "+store.getName());
        }


    }

...

Now all the store rows matching the id are in the list. The expected result should apply where clause too

I turned on logging for hibernate 5.3.9 There is no where clause when it fires the select query

Possible duplicate of [How to use @Where in Hibernate](https://stackoverflow.com/questions/7700071/how-to-use-where-in-hibernate) — Simon Martinelli, Jun 26 '19 at 12:00
I have read up by searching internet the use of "Where" annotation along with "OneToMany". I would like to do similar task with "ManyToOne" annotation too. "Where" annotation doesnt seem to be used by hibernate when it generates the select query — IT guy, Jun 27 '19 at 07:01
Why would you do want to do that? Because you can already exclude these entities will querying — Simon Martinelli, Jun 27 '19 at 08:52
Assume that I have the store user logging in. After his credentials are verified, I want to check the auth information. I dont want to get soft deleted auth information of the store user. I am not an expert in JPA, Spring. If you point out to the correct information, i will be grateful, @SimonMartinelli — IT guy, Jun 27 '19 at 09:19
I can only see toOne relationships on StoreUserAuthModel . So if you want to get the StoreUserAuthModel without soft deleted you can set the @Where on the class level. Is that what you want? — Simon Martinelli, Jun 27 '19 at 09:47
I am not sure if I made it clear. I am afraid i didnt get what you meant. The storeModel, Store Role and user have bidirectional relation with storeUserAuthModel. I have got the store user. Through the store user I access the StoreUserAuthModel associated with the store user. I would like make sure the list of storeUserAuthModel are the ones which are not soft deleted(ie. storeUserAuthModel.isdeleted = 0) and store and store role are also not softdeleted(ie StoreModel.isdeleted = 0 and StoreRoleModel.isdeleted = 0 ). I think you got my point. — IT guy, Jun 28 '19 at 05:45

score 0 · Accepted Answer · answered Jun 28 '19 at 05:51

The @Where annotation has no effect on ToOne relationships. But instead of adding @Where to the reference you can use @Where on the Entity:

@Where(clause="isdeleted = 0")
@Entity
@Table(name = "storerole")
public class StoreRoleModel {

That way no deleted entities of StoreRoleModel will be loaded by Hibernate.

Can I use @Where annotation along with @ManytoOne association?

1 Answers1