Wednesday, August 22, 2007

Mixing AND and OR conditions in entityConditionList

How does one make a query using findByCondition that has both OR and AND clauses?

hint: do a search on .OR in your ofbiz code. This will lead you to
the file lookupBulkAddProducts.bsh

with the following code:

conditionList.add(new EntityExpr("productTypeId", EntityOperator.NOT_EQUAL, "AGGREGATED"));
than isVirtual != "Y".
// we consider those products to be non-virtual and hence addable to the order in bulk
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.NOT_EQUAL, "Y"));
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.EQUALS, "N"));
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.EQUALS, null));

orConditions = new EntityConditionList(orConditionList, EntityOperator.OR);
conditions = new EntityConditionList(conditionList, EntityOperator.AND);

mainConditions = new EntityConditionList(mainConditionList, EntityOperator.AND);

productList = delegator.findByCondition("Product", mainConditions, UtilMisc.toList("productId", "brandName", "internalName"), UtilMisc.toList("productId"));

pretty self-explanatory. Group the OR statements in their own entityConditionList, the AND statements in their own, and combine the two in a master EntityConditionList (using the EntityOperator.AND)
so, the above would result in something like WHERE (a OR b OR c) AND d

1 comment:

JKK said...

Pretty cool! Thanks for that.

Is there anyway we can do INNER JOIN using EntityCondition or Expr?