Search code examples
guidewiregosu

Ordering query results on expressions referencing properties of other entities

In the Ordering results subsection of the Query builder APIs part of the Gosu reference guide, there is a listing showing how to order the result set of a simple query, based on (database-backed) properties of other entities. For example, to sort CC notes based on the property Activity#LastViewedDate, we can simply write

uses gw.api.database.Query 
uses gw.api.database.QuerySelectColumns
uses gw.api.path.Paths

var queryNotes = Query.make(Note)  // Query for notes
var resultNotes = queryNotes.select()

// Sort the notes by related date on activity
resultNotes.orderBy(QuerySelectColumns.path(Paths.make(Note#Activity, Activity#LastViewedDate)))

Is there any way to order the result set of a query based on calculated properties in a situation when it's necessary to join the related entity to the main query? For example, given the code shown below, is it possible for the DATEDIFF clause to reference the property Claim#LossDate?

function loadExposures(asOfDate : Date, policyNumber : String) : IQueryBeanResult<Exposure> {
    var queryExposure = Query.make(Exposure)
    var claimTable = queryExposure.join(Exposure#Claim)
    var policyTable = claimTable.join(Claim#Policy)

    policyTable.compare(Policy#PolicyNumber, Relop.Equals, policyNumber)

    var selectExposure = queryExposure.select()
    selectExposure.orderBy(QuerySelectColumns.dbFunction(DBFunction.Expr({"DATEDIFF(''days'', ", queryExposure.getColumnRef("Claim.LossDate"), ", ''${asOfDate}'')"})))
    selectExposure.thenBy(QuerySelectColumns.path(Paths.make(Exposure#Claim, Claim#ClaimNumber)))
    selectExposure.thenBy(QuerySelectColumns.path(Paths.make(Exposure#ClaimOrder)))

    return selectExposure
}

Obviously, queryExposure.getColumnRef("Claim.LossDate") doesn't work because the LossDate column can only be accessed through the reference claimTable. However, writing claimTable.getColumnRef("LossDate") won't work either because the root type of the claimTable object is entity.Claim. Is there any way to fix this?

Solution

  • Can I ask what the specific problem you are trying to address is? Here is my assumption:

    From the function name and arguments it appears like you are trying to load all exposures related to a certain PolicyNumber and then orderBy (ascending implied) the result of the DATEDIFF function comparing the Claim LossDate to your passed in argument. All LossDate's before the passed in value will have a positive value from the DATEDIFF function and all LossDate's after the passed in value will have a negative value. So this ordering is essentially the same as simply ordering the exposures by Claim LossDate (descending). I think you can achieve what you want to do by:

    selectExposure.orderByDescending(QuerySelectColumns.path(Paths.make(Exposure#Claim, Claim#LossDate)))

    In this case you wouldn't even need to pass in an 'asOfDate' argument, unless you wanted to use this filter out some records based on that date.