def result = [:] params.max = Math.min(params?.max?.toInteger() ?: 10, paramsMax) params.offset = params?.offset?.toInteger() ?: 0 /** * HqlBuilder. * HqlBuilder Draft B, there are likely still some gains to be had. * Although not specifically built for speed HqlBuilder is used in production without problem. * The example query is a real world example that can be easily represented in Criteria Builder, * select 'new Map(inventoryItem.name, inventoryItem.unitsInStock)' anyone? */ params.sort = "inventoryItem." + (params?.sort ?: "name") params.order = params?.order == "desc" ? "desc" : "asc" def q = new HqlBuilder(max: params.max, offset:params.offset).query { select 'count(distinct inventoryItem) as inventoryItemCount' from 'InventoryItem as inventoryItem', 'left join inventoryItem.alternateSuppliers as alternateSupplier' where 'inventoryItem.unitsInStock <= inventoryItem.reorderPoint' and 'inventoryItem.isActive = true' and 'inventoryItem.isObsolete = false' if(!params.includeReorderListingDisabled) and "inventoryItem.enableReorderListing = true" if(params.selectedSupplier.isLong()) { namedParams.supplier = Supplier.get(params.selectedSupplier.toLong()) if(params.includeAlternateSuppliers) and "(inventoryItem.preferredSupplier = :supplier or alternateSupplier = :supplier)" else and "inventoryItem.preferredSupplier = :supplier" } // if selectedSupplier if(params.selectedGroups) { namedParams.selectedGroupIds = params.selectedGroups and "inventoryItem.inventoryGroup.id in(:selectedGroupIds)" } if(!params.includeOnBackOrder) { // Sub query! Not shown in Criteria or String Builder. def onBackOrder = new HqlBuilder().query { from "InventoryItemPurchase p" where "p.inventoryItem = inventoryItem" and "p.inventoryItem = inventoryItem" and "p.inventoryItemPurchaseType.id = 1" // Order Placed. and "p.receivedComplete = false" and "p.date > :oneMonthAgo" } namedParams.oneMonthAgo = new Date() - 30 and "not exists ($onBackOrder.query)" } } // query def totalCount = InventoryItem.executeQuery(q.query, q.namedParams)[0].toInteger() q.select = 'distinct inventoryItem' q.order = "by $params.sort $params.order, inventoryItem.id asc" def list = InventoryItem.executeQuery(q.query, q.namedParams, q.paginateParams) result.inventoryItemList = new PagedResultList(list, totalCount) /** * Criteria Builder. * Assuming that we don't need to check totalCount before fetching. * Not doing multiple sort/order by. * Exactly how many where conditions do we have in this query? */ params.sort = params?.sort ?: "name" params.order = params?.order ?: "asc" result.inventoryItemList = InventoryItem.createCriteria().list( max: params.max, offset: params.offset, sort: params.sort, order: params.order) { eq("isActive", true) eq("isObsolete", false) if(!params.includeReorderListingDisabled) eq("enableReorderListing", true) leProperty("unitsInStock", "reorderPoint") if(params.selectedSupplier.isLong()) { or { preferredSupplier { eq("id", params.selectedSupplier.toLong()) } if(params.includeAlternateSuppliers) { alternateSuppliers { eq("id", params.selectedSupplier.toLong()) } } } } // if selectedSupplier if(params.selectedGroups) { inventoryGroup { inList("id", params.selectedGroups) } } // if selectedGroups } // createCriteria /** * String Builder. * Attempting to be DRY results in a pre selected sequence that is hard to change. * High chance of missing a space, comma, namedParams, order or sort. * No pretty format to review or present query. * Readability? */ params.sort = "inventoryItem." + (params?.sort ?: "name") params.order = params?.order == "desc" ? "desc" : "asc" def namedParams = [:] def paginateParams = [max:params.max, offset:params.offset] def qBase = "from InventoryItem as inventoryItem " qBase += "left join inventoryItem.alternateSuppliers as alternateSupplier " qBase += "where inventoryItem.unitsInStock <= inventoryItem.reorderPoint " qBase += "and inventoryItem.isActive = true " qBase += "and inventoryItem.isObsolete = false " if(!params.includeReorderListingDisabled) qBase += "and inventoryItem.enableReorderListing = true " if(params.selectedSupplier.isLong()) { namedParams.supplier = Supplier.get(params.selectedSupplier.toLong()) if(params.includeAlternateSuppliers) qBase += "and (inventoryItem.preferredSupplier = :supplier or alternateSupplier = :supplier) " else qBase += "and inventoryItem.preferredSupplier = :supplier " } // if selectedSupplier if(params.selectedGroups) { namedParams.selectedGroupIds = params.selectedGroups qBase += "and inventoryItem.inventoryGroup.id in(:selectedGroupIds) " } def qCount = "select count(distinct inventoryItem) as inventoryItemCount " + qBase def totalCount = InventoryItem.executeQuery(qCount, namedParams)[0].toInteger() def qSelect = "select distinct inventoryItem " + qBase qSelect += "order by $params.sort $params.order, inventoryItem.id asc " def list = InventoryItem.executeQuery(qSelect, namedParams, paginateParams) result.inventoryItemList = new PagedResultList(list, totalCount) // THE END.