Ebean query for string that startsWith then contains a specific string

Question

I got a little issue here with a query for a string to search in DB for auto-complete functionality, the problem that I got that I need first results to be all statements that "Start" with then the statements that contain that string!

For example: If I got the following records in a table in the database:

1. Awesome service to provide
2. Something else here
3. Extra Service
4. My Service is awesome
5. Service Provided
6. Zoo Services

And I started to type "Service" I need No. 5 to show first, then the rest of items ( 1, 3, 4, 6)

I know how to do that in MySQL, something like:

select * from `services` where `name` like '%Service%' order by `name` like 'Service%' desc;

Or maybe like the ways mentioned here: MySQL order by "best match"

but I need to do that in Ebean. I tried:

return Service.find.query().where().icontains("name", search).findList()

But this return with 1, 3, 4, 5, 6,

and now I do the following:

final List<Service> list = Service.find.query().where()
            .istartsWith("name", search)
            .orderBy("name").findList(); // First query 

            list.addAll(Service.find.query().where()
            .icontains("name", search)
            .not().istartsWith("name", search)
            .orderBy("name").findList()); // Then add the results for the second query
return list;

This will do the job, 5, 1, 3, 4, 6 but I don't like it, because I hit database with 2 queries, also I do exclude the first query in complex data type query in the second query, but anyway, is it a correct way? if not, do you have a suggestion or solution for a better way?

Table:

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE services ADD UNIQUE `uq_services_name`(name);

EBean entity Service.java:

@Entity
@Table(name = "services")
public class Service extends BaseModel<Service> {
    public static Finder<Integer, Service> find = new Finder<>(Service.class);

    @Column(nullable = false, unique = true)
    private String name;

    // ... setters and getters

}

BaseModel.java:

@MappedSuperclass
public abstract class BaseModel<T> extends Model {

    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    public final Integer getId() {
        return id;
    }

    @SuppressWarnings("unchecked")
    public final T setId(Integer id) {
        this.id = id;
        return (T) this;
    }
}

Answer 1

I believe the issue is the order by name like 'Service%' desc; part.

• You could just use findNative(sql)

• There was also a recent change to Ebean to allow arbitrary functions in the order by clause so you could try updating your Ebean version and doing:

  .orderBy("name like 'Service%' desc").findList();

Answer 2

Mothafar.

That's a good question. I guess you don't like your solution because it queries the database twice. So what about querying first and then sorting afterwards:

final List<Service> list = Service.find.query().where()
        .icontains("name", search)
        .findList()
        .sort(Comparator.comparingInt(service -> service.getName().indexOf(search)))
);
return list;

This way #5 comes first and then the rest ordered by the appearance index of the search term. The caveat is that the sorting is not done through the database. But maybe it is possible to integrate such condition into the orderBy-clause.

Cheers

Jens