dominicbri7 dominicbri7 - 3 months ago 15
Scala Question

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

Answer

I finally made it work, had to join the 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]]
    )
)