Argthtjtr

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;

    }

}

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