How to query with multiple joins in Guidewire?

Question

Dear Stackoverflow GW Community,

I am trying to efficiently query the database for contacts that have a specific role and are connected to (a) specific claim(s).

To my mind, the obvious way to do something like this in SQL would be to join Claim and ClaimContactRole on ClaimContact and do the filtering. So, I tried the following:

var relevantRoles = {
    ContactRole.TC_INSURED,
    ContactRole.TC_DRIVER,
    ContactRole.TC_WITNESS,
    ContactRole.TC_CLAIMANT
}

var relevantContacts = Query.make(ClaimContact)
    .join("Claim", Claim, "ID")
    .join("Contact", Contact, "ID")
    .join("ID", ClaimContactRole, "ClaimContact")
    .join("Role", ContactRole, "ID")
    .compare(Claim#ID, Equals, claimUnderReview.ID)
    .compareIn("Role", relevantRoles.toArray()).select()*.Contact.toList()

Unfortunately, the join() operation fails on the second entity with

java.lang.IllegalArgumentException: Column not found: Contact .

What would be the correct approach to filter on DB level instead of loading and filtering all information in memory?

Answer 1

The problem seems to be in the last line below:

var relevantContacts = Query.make(ClaimContact)
    .join("Claim", Claim, "ID")
    .join("Contact", Contact, "ID")

Once you join with Claim, the successive join statement expects that the join is being made with respect to Claim entity and not ClaimContact. Since Claim has no column called "Contact" hence the error. I think you are doing some extra joins which are not required and here's a simplified version of the query to achieve the same result

var relevantContacts = gw.api.database.Query.make(ClaimContactRole)
    .compareIn(ClaimContactRole#Role, relevantRoles.toArray())
    .join(ClaimContactRole#ClaimContact)
    .join(ClaimContact#Claim)
    .compare(Claim#ID, Equals, claimUnderReview.ID)
    .select()*.Contact.toList()

Edit: My answer is to effectively get rid of the join on Contact as suggested in Steve's answer. I missed his answer as I had a draft open :)

Answer 2

To answer the question about the illegal argument exception, I believe this is the reason for the error. If you don't chain the join statements you see the issue.

var claimContactQuery = Query.make(ClaimContact)
var claimTable = claimContactQuery.join("Claim", Claim, "ID")
var contactTable = claimTable.join("Contact", Contact, "ID") //There is no Contact column in the Claim entity

I think the better approach will be to not join on the Contact entity at all. It seems you just want to limit your results by ClaimNumber and an array of roles, and this information is not stored on the Contact entity. When the ClaimContact entities are loaded from the DB (after calling .select()) you will still have access to all the Contact fields with dot notation (e.g. ClaimContact.Contact.SomeContactProperty).

Alternatively to filtering the roles in the DB you could call claim.getContactsWithPreload() and filter out the unwanted contacts in application code. There would be somewhat of a performance loss, but probably not that much unless the claim in question has hundreds of contacts associated with it.