Attach two List expressions with "or" or "and",

Question

I am having trouble connecting 2 List expressions with "or". This is an example of what I am doing:

RelationGroup prg =... ExpressionList<User> exp = User.find.where(); List<ExpressionList<User>> expressions = new ArrayList<ExpressionList<User>>() List<String> relations = new ArrayList<String>() while(prg != null){ if(prg.prevGroupRelation != null) relations.add(prg.prevGroupRelation); for(RelationAtt pra : prg.prAtt){ if(pra.rel.equals("eq")) exp = exp.eq(pra.name, pra.value1 ); else if(pra.rel.equals("lt")) exp = exp.lt(pra.name, pra.value1); else if(pra.rel.equals("gt")) exp = exp.gt(pra.name, pra.value1); else if(pra.rel.equals("bw")) exp = exp.gt(pra.name, pra.value1).lt(pra.name, pra.value2); } expression.add(exp); prg=prg.nextPRG(); exp = new ExpressionList<User>(); } for(i=0;i<expressions.count-1; i++) if(relations[i].equals("or")){ //ToDo: (expressions[i]) or (expressions[i+1]) }else{ //ToDo: (expressions[i]) and (expressions[i+1]) } 

I need something like:

 select * from tableName where (varName1=value and varName2=value) or (varName3) or (varName4=value and varName5=value) 

This is completely dynamic, so varNames can be any of the existing ones (since the queries are created by the user using the web page interface), so I cannot easily use raw SQL. Now I need to join prevExp and exp with or or and replace exp. ExpressionList.or(exp, exp) get 2 expressions. Any help with this appreciated thanks

Answer 1

You need to check the Ebean Junctions function .

There are two types of transitions, conjunctions and disjunctions. With conjunction, you can combine many expressions with AND, and with Disjunction you combine many expressions with OR.

For instance:

 Query q = Ebean.createQuery(YourClass.class); q.where().disjunction() .add(Expr.eq("varName1",value).eq("varName2",value)) .add(Expr.eq("varName3",value3)) .add(Expr.eq("varName4",value4).eq("varName5",value5)) 

generate sql as follows:

 SELECT * FROM tablename WHERE (varName1=value and varName2=value) or (varName3=value3) or (varName4=value4 and varName5=value5) 

If you need flexibility in choosing a connection type, you can do this:

 Query q = Ebean.createQuery(YourClass.class); Junction<YourClass> junction; if("or".equals(desiredJunctionType)){ junction = query.where().disjunction(); } else { junction = query.where().conjunction(); } // then you can add your expressions: junction.add(Expr.eq("varName1",value).eq("varName2",value)); junction.add(Expr.eq("varName3",value3)); // etc... // and finaly get the query results List<YourClass> yourResult = junction.query().findList(); 

Answer 2

I found that seems to be the same question on google groups , so let's all get this answer here as well.

 List<MobileUser> users = Ebean.find(MobileUser.class) .where() .disjunction() .conjunction() .eq("col1", val) .eq("col2", val) .endJunction() .eq("col3", anotherValue) .conjunction() .eq("col4", val) .eq("col5", val) .endJunction() .endJunction() .findList(); 

Thank you, James, for making my request work the way I intended.