How to query with multiple joins in Guidewire?

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?

Solution

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 :)