I have the following model;
- I have users and rules
- a user can be added to 0, 1 or more rules
- a rule can contain 0, 1 or more users
Here is the UserEntity class:
Code:
class UserEntity {
private String username;
private List<RuleEntity> rules;
@Column(name = "username", nullable = false, unique = true)
public String getUsername() {
return username;
}
@ManyToMany(mappedBy="users" , fetch = FetchType.LAZY)
public List<RuleEntity> getRules() {
return rules;
}
...
}
And the RuleEntity class:
Code:
class RuleEntity {
private String name;
private List<UserEntity> users;
@Column(name = "name", nullable = false)
public String getRuleName() {
return ruleName;
}
@ManyToMany (fetch = FetchType.LAZY)
@JoinTable(name= "RULE_USER" ,joinColumns=@JoinColumn
(name=RuleEntity.RULE_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false),
inverseJoinColumns=@JoinColumn
(name=UserEntity.USER_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false),
uniqueConstraints = @UniqueConstraint(columnNames = {RuleEntity.RULE_ID, UserEntity.USER_ID}))
public List<UserEntity> getUsers() {
return users;
}
...
}
I am trying to implement a search whereby a user can search using:
- only a username
- only a rulename
- a username and a rulename
So I am performing 2 HQL queries, one to return the matching users and one to return the matching rules e.g.
Code:
public SearchResults search(String maybePartialUsername, String maybePartialRuleName) {
List<UserEntity> userEntities = hqlQuery("select distinct users from UserEntity as users inner join users.rules as rules where users.username like :maybePartialUsername and rules.ruleName like :maybePartialRuleName");
List<RuleEntity> ruleEntities = hqlQuery("select distinct rules from RuleEntity as rules inner join rules.users as users where users.username like :maybePartialUsername and rules.ruleName like :maybePartialRuleName");
return SearchResults(userEntities, ruleEntities);
}
The first HQL query for finding users matching a username (and/or rulename) works fine when a user is a member of at least one rule, but it returns no results when a user has not been added to any rules.
Changing the 'inner join' to a 'left join' does not help. The problem lies in the 'rules.ruleName like :maybePartialRuleName' condition, if I remove this the query works, but I need this in the query in the cases where the join with the rules tables succeeds (i.e. a user DOES have rules), and thus I then need to filter by rule name as well as username.
Code:
select distinct users from UserEntity as users inner join users.rules as rules where users.username like :maybePartialUsername and rules.ruleName like :maybePartialRuleName
Login
FAQ
Search