FYI: How to set multiple conditions programmatically for a Query

Support
#1

It took me a while to figure out how to set multiple conditions programmatically for a complex non-Entity Query. Especially when I want conditions to be omitted whenever the parameter is not provided.
The code below comes from my Groovy script in my Report band but it may still be useful documentation:
It uses setCondition() method

import com.company.kairosmd.entity.VisitTransaction
import com.haulmont.cuba.core.entity.KeyValueEntity
import com.haulmont.cuba.core.global.ValueLoadContext
import com.haulmont.cuba.core.global.queryconditions.JpqlCondition
import com.haulmont.cuba.core.global.queryconditions.LogicalCondition

def result = []
def paramLocation = params['location']
def paramCurrency = params['currency']
def paramInsurance = params['insurance']
def paramCostCenter = params['costCenter']

LogicalCondition conditions = new LogicalCondition(LogicalCondition.Type.AND)
conditions.add(new JpqlCondition("o.visit.location = :location"))
conditions.add(new JpqlCondition("o.visit.currency = :currency"))
conditions.add(new JpqlCondition("ins = :insurance"))
conditions.add(new JpqlCondition("o.costCenter = :costCenter"))

ValueLoadContext context = ValueLoadContext.create()
    .setQuery(ValueLoadContext.createQuery(
        "select " +
        "o.visit.location.code, " +
        "vp.paymentDate, " +
        "o.visit.visitDate, " +
        "o.visit.patient.patientNo, " +
        "o.visit.patient.name, " +
        "o.costCenter.code, " +
        "CASE " +
        "    WHEN (vp.isPatientPayment = 1) " +
        "        and (o.visit.whoPays = @enum(com.company.kairosmd.entity.WhoPaysEnum.PATIENT)) THEN '<Patient>' " +
        "    WHEN (vp.isPatientPayment = 1) " +
        "        and (o.visit.whoPays = @enum(com.company.kairosmd.entity.WhoPaysEnum.GUARANTOR)) THEN g.name " +
        "    WHEN (vp.isPatientPayment = 0) THEN ins.name " +
        "ELSE '???' " +
        "END, " +
        "o.visit.currency.ccy, " +
        "(o.amount * -1), " +
        "vp.paymentType.name, " +
        "ac.name, " +
        "vp.reference, " +
        "vp.receiptNo " +
        "from kairosmd_VisitTransaction o " +
        "LEFT JOIN o.visitPayment vp " +
        "LEFT JOIN o.visit.guarantor g " +
        "LEFT JOIN o.insurance ins " +
        "LEFT JOIN o.visitPayment.applyToCharge ac " +
        "where (o.type = 'P')")
            .setCondition(conditions)
            .setParameter("location", paramLocation)
            .setParameter("currency", paramCurrency)
            .setParameter("insurance", paramInsurance)
            .setParameter("costCenter", paramCostCenter)
        )
        .addProperty("location")
        .addProperty("paymentDate")
        .addProperty("visitDate")
        .addProperty("patientNo")
        .addProperty("patientName")
        .addProperty("costCenter")
        .addProperty("payer")
        .addProperty("currency")
        .addProperty("amount")
        .addProperty("paymentType")
        .addProperty("applyToCharge")
        .addProperty("reference")
        .addProperty("receiptNo");
        
List<KeyValueEntity> list = dataManager.loadValues(context);

for (KeyValueEntity kv: list) {
    String loc = kv.getValue("location")
    Date paymentDate = kv.getValue("paymentDate")
    Date visitDate = kv.getValue("visitDate")
    Integer patientNo = kv.getValue("patientNo")
    String patientName = kv.getValue("patientName")
    String costCenter = kv.getValue("costCenter")
    String payer = kv.getValue("payer")
    String currency = kv.getValue("currency")
    BigDecimal amount = kv.getValue("amount")
    String paymentType = kv.getValue("paymentType")
    String applyToCharge = kv.getValue("applyToCharge")
    String reference = kv.getValue("reference")
    Integer receiptNo = kv.getValue("receiptNo")
    
    result.add([
        'location': loc,
        'paymentDate': paymentDate,
        'visitDate': visitDate,
        'patientNo': patientNo,
        'patientName': patientName,
        'costCenter': costCenter,
        'payer': payer,
        'currency': currency,
        'amount': amount,
        'paymentType': paymentType,
        'applyToCharge': applyToCharge,
        'reference': reference,
        'receiptNo': receiptNo
    ])
}

return result
1 Like