join two expressionList with "or" or "and"

Question

I'm having some trouble to join 2 expressionList with an "or". This is an example of what I'm 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 to have something like:

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

This is fully dynamic so the varNames can be any of the currently existing(because the queries are build by the user using a web page interface), so i can't use raw SQL that easily. Now i need to join prevExp and exp with an or/and and replace exp. The ExpressionList.or(exp, exp) receive 2 Expressions. Any help with this is appreciated thank you

Answer 1

I found, what seems to be, the same question on google groups, so lets all enjoy that 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();

Thanks you James for getting my query to work as I intended it.

Answer 2

You need to check out the Ebean Junctions feature.

There are two kinds of Junctions, Conjuction and Disjunction. With a conjunction you can join together many expressions with AND, and with a Disjunction you join together many expressions with OR.

For example:

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 a sql like this:

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

If you want the flexibility to select the type of junction 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();