Hibernate subquery of String collection match
I have a Company entity which has a legalName: String property and also an aliases: Set<String> property (so a One-to-many) Company would be the parent each alias String in the aliases collection would be the children
I successfully made a query that returns all companies (parents) which match the parent legalName property with a search query string (LIKE), but I also want to return companies (parents) that have an alias (children) that matches the search query string as well (all companies that either match the legal name, or at least one of the aliases, or both)
I apologize in advance because I feel like this is a very simple case, and I'm very used to writing plain SQL and this is my first time ever playing with Hibernate or any JPA implementation
Here's my code
val em: EntityManager = styxDao.getEntityManager
val criteriaBuilder: CriteriaBuilder = em.getCriteriaBuilder
val cq: CriteriaQuery[CompanyEntity] = criteriaBuilder.createQuery(classOf[CompanyEntity])
val companyEntityType: EntityType[CompanyEntity] = em.getMetamodel.entity(classOf[CompanyEntity])
val companyEntityRoot: Root[CompanyEntity] = cq.from(classOf[CompanyEntity])
var query: CriteriaQuery[CompanyEntity] = cq.select(companyEntityRoot)
if (countryCode == null) {
query = query.where(
criteriaBuilder.or(
criteriaBuilder.like( // if legalName matches ("LIKE") the search string
criteriaBuilder.lower(companyEntityRoot.get(companyEntityType.getDeclaredSingularAttribute("legalName", classOf[String]))),
"%" + legalNameQuery.toLowerCase + "%"
).asInstanceOf[Expression[jBoolean]],
// if any of the aliases match ("LIKE") the search string
)
)
}
The aliases isn't an entity per-se, it's just a collection of strings, I just want to add a second criteria in my OR that returns true if any of the aliases matches the same legalNameQuery search string
As I mentioned earlier, if I remove the OR part and just run the legalName string match, it works, what doesn't work is the second part of the OR that would match the aliases
我终于搞定了，不得不join aliases
query = query.where(
criteriaBuilder.or(
criteriaBuilder.like( // if legalName matches ("LIKE") the search string
criteriaBuilder.lower(companyEntityRoot.get(companyEntityType.getDeclaredSingularAttribute("legalName", classOf[String]))),
"%" + legalNameQuery.toLowerCase + "%"
).asInstanceOf[Expression[jBoolean]],
criteriaBuilder.like (
criteriaBuilder.lower (companyEntityRoot.join("aliases") ),
"%" + legalName.toLowerCase + "%"
).asInstanceOf[Expression[jBoolean]]
)
)