Ebean using OR in query

Question

I'm trying to make a query where I want to check if either the email or name of a user starts with a given string. In a sql query I would write this using

name like 'queryString%' or email like 'queryString%'

In ebean query I would expect to write something like:

find.where().or(like('name', 'queryString%'), like('email', 'queryString%'));

The problem is that the or takes in an expression, not an expressionlist, which is what I get when writing

find.where().like(...,...)

As I understand it doing a query like this:

find.where().like(.., ...).like(..., ...)

is using AND.

How can I write such a query using ebean?

Thanks!

Enameling

Answer 1

Your second attempt is almost OK, you have to use just com.avaje.ebean.Expr.like() inside the or()

find.where().or(
        com.avaje.ebean.Expr.like("email", email + "%"),
        com.avaje.ebean.Expr.like("name",  name + "%")
).findUnique(); 

of course you can use an import for shorter code::

import com.avaje.ebean.Expr;

...
find.where().or(Expr.like("email", email + "%"),Expr.like("name", name + "%")).findUnique();

Check the API in Javadoc: http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/Expr.html

Cripps

I could not find this documented anywhere on the avaje ebean docs page, and I'm new to ebean. Thanks for the answer though!;) – Enameling

Your example shows two alternatives. How do you OR more than 2 things? – Faldstool

@TomL, I'm afraid that only sensible way is building queries with avaje.org/static/javadoc/pub/com/avaje/ebean/RawSql.html - it's not as comfortable as common finders, however it's pretty flexible. Otherwise you'd need to wrap or with or with or In case when you have fully dynamic amount of conditions to check wrapping or expressions makes no sense. – Cripps

Answer 2

Note that you can also use fluid style via disjunction(), conjunction() and endJunction().

For example:

Query<Conversation> query = Ebean.find(Conversation.class)
  .where().eq("group.id", groupId)
  .disjunction()
    .conjunction()
      .eq("open", false).eq("participants.user.id", userId)
    .endJunction()
    .eq("open", true)
  .endJunction()
  .orderBy("whenCreated desc");

And an example using type safe query beans it is similar but instead uses or(), and(), endAnd(), endOr() ... rather than disjunction()/conjunction() etc.

List<Customer> customers
  = new QCustomer()
  .billingAddress.city.equalTo("Auckland")
  .version.between(1,100)
  .billingAddress.country.equalTo(nz)
  .registered.before(new Date())
  .or()
    .id.greaterThan(1)
    .and()
      .name.icontains("Jim")
      .inactive.isTrue()
    .endAnd()
  .endOr()
  .orderBy()
    .name.asc()
    .id.desc()
  .findList();

Garik

Answer 3

Note that the query can be written in fluid style using disjunction():

find.where().disjunction()
  .like("email", email + "%")
  .like("name",  name + "%")
  .findUnique(); 

If there is more that one disjunction() / conjunction() then you use endJunction() to end that OR and AND group.

In the example there is only a single OR group required so endJunction() is not needed.

Garik

Thanks for this. I was trying to figure out how to do a nested series of AND/OR clauses in the query. This seems to be the way to do it. – Nika

disjunction() and conjunction() gives much more flexibility than or() or and() especially when there is requirement for using more than 2 expressions in each. – Cripps

Answer 4

you can use raw(String,Object[]) see Raw expressions :

new QMyEntity().raw("name like ? or email like ?","yourName","yourEmail").findOne();

Beograd

Answer 5

about more then 2 OR LIKE, you can try this...

where.or(Expr.contains("name", inquire.q),
                    Expr.or(Expr.contains("address", inquire.q), Expr.contains("description", inquire.q)));

Prau