FullTextQuery.setCriteriaQuery and Order.desc / Order.asc

Jaba · **Posted:** Thu Aug 06, 2009 10:45 am

Hello everyone! Please refer to code below.
SearchService

Code:

                FullTextQuery ftQuery = fullTextSession.createFullTextQuery(luceneQuery);
                Criteria criteria = session.createCriteria(searchEntity);
                int begin = page * (int) itemsPerPage;
                criteria.setMaxResults(itemsPerPage);
                criteria.setFirstResult(begin);
                // Sort sort = null;
                if (order != null && sortField != null)
                {
                    if ("DESC".equalsIgnoreCase(order))
                    {
                        criteria.addOrder(Order.desc(sortField));
                        //sort = new Sort(sortField, true);
                    }
                    else
                    {
                        criteria.addOrder(Order.asc(sortField));
                        //sort = new Sort(sortField, false);
                    }
                    //ftQuery.setSort(sort);
                }
                ftQuery.setCriteriaQuery(criteria);
                List<T> results = ftQuery.list();

Snippet of the searchEntity. Please note, that launchDate field is not indexed (it is not required)

Code:

@Indexed
public class CatalogueItem extends AbstractDAOItem
{
    @Field
    private String orgName;
    @Field
    private String email;

    private Date launchDate;

    // getters and setters
}

The criteria.addOrder(Order.asc(sortField)); code doesn't work because of the following QueryLoader code:

Code:

      criteria.list(); //load all objects

      //mandatory to keep the same ordering
      List result = new ArrayList( entityInfos.length );
      for (EntityInfo entityInfo : entityInfos) {
         Object element = session.load( entityInfo.clazz, entityInfo.id );
         if ( Hibernate.isInitialized( element ) ) {
            //all existing elements should have been loaded by the query,
            //the other ones are missing ones
            result.add( element );
         }
      }
      return result;

So, the questions are:
1. Please tell me the right way to use ordering in case of FullTextQuery (especially for fields like launchDate - which are not required for indexing).
2. The commented code (with sort = new Sort(sortField, true);) doesn't sort results in case of sortField is orgName or email. And of course it sucks with launchDate field). It seems an additional question to #1.
3. I whould like to use the possibility of ordering results in case of Order.desc() or Order.asc(). I have patched the FullTextQuery, FullTextQueryImpl and QueryLoader. Please see the snippets:

FullTextQuery

Code:

public interface FullTextQuery extends Query, ProjectionConstants {
    
    ...

    // HELPFULL FOR ORDER 
    void setUseCriteriaOrderBy(boolean useCriteriaOrderBy);

}

FullTextQueryImpl

Code:

public class FullTextQueryImpl extends AbstractQueryImpl implements FullTextQuery {

    private boolean useOrderBy = false;
    ...
    
    private Loader getLoader(Session session, SearchFactoryImplementor searchFactoryImplementor) {

            ...

            QueryLoader loader = new QueryLoader();
            loader.init( session, searchFactoryImplementor );
            loader.setUseCriteriaOrderBy(useOrderBy); // <<<<<<<<<<<
            loader.setEntityType( classes[0] );
            loader.setCriteria( criteria );
            return loader;

            ...
    }
    

    public void setUseCriteriaOrderBy(boolean useOrderBy) {
        this.useOrderBy = useOrderBy;
    }
}

Code:

public class QueryLoader implements Loader {
    private boolean useCriteriaOrderBy = false; // ORDER BY QUICK FIX
    ...

    public List load(EntityInfo... entityInfos) {
        final int maxResults = entityInfos.length;
        if ( maxResults == 0 ) return EMPTY_LIST;
        if ( entityType == null ) throw new AssertionFailure( "EntityType not defined" );
        if ( criteria == null ) criteria = session.createCriteria( entityType );

        DocumentBuilder builder = searchFactoryImplementor.getDocumentBuilders().get( entityType );
        criteria.add( disjunction );
        List result = criteria.list(); //load all objects
        
        // ORDER BY 
        if (!useCriteriaOrderBy) {
            //mandatory to keep the same ordering
            result = new ArrayList( entityInfos.length );
            for (EntityInfo entityInfo : entityInfos) {
                Object element = session.load( entityInfo.clazz, entityInfo.id );
                if ( Hibernate.isInitialized( element ) ) {
                    //all existing elements should have been loaded by the query,
                    //the other ones are missing ones
                    result.add( element );
                }
            }
        }
        return result;
    }

    ...

    public void setUseCriteriaOrderBy(boolean useOrderBy) {
        this.useCriteriaOrderBy = useOrderBy;
    }

}

And after that it may be used like this:

Code:

                FullTextQuery ftQuery = fullTextSession.createFullTextQuery(luceneQuery);
                Criteria criteria = session.createCriteria(searchEntity);
                int begin = page * (int) itemsPerPage;
                criteria.setMaxResults(itemsPerPage);
                criteria.setFirstResult(begin);
                // Sort sort = null;
                if (order != null && sortField != null)
                {
                    if ("DESC".equalsIgnoreCase(order))
                    {
                        criteria.addOrder(Order.desc(sortField));
                        //sort = new Sort(sortField, true);
                    }
                    else
                    {
                        criteria.addOrder(Order.asc(sortField));
                        //sort = new Sort(sortField, false);
                    }
                    ftQuery.setUseCriteriaOrderBy(true); // <<<<<<<<<<<<<<<<<<<<
                    //ftQuery.setSort(sort);
                }
                ftQuery.setCriteriaQuery(criteria);
                List<T> results = ftQuery.list(); // ordered list;

So, is it possible to add this functionality to Hibernate Search? And why not?

Thank you.

sanne.grinovero · **Posted:** Thu Aug 13, 2009 12:23 pm

Hi, this is interesting but I'm afraid it won't work: search will use the Loader to initialize only the entities which are going to be returned, but using firstResult and maxResults you could get a "window" from the full list.
The ordering from the full list is done by Lucene itself, so you must use Lucene Sort and sort on indexed fields, otherwise you would only sort the page you're looking at, but trying to fetch the "next page" will load a list of objects which are not guaranteed to be "after" the previous ordered list.

Quote:

1. Please tell me the right way to use ordering in case of FullTextQuery (especially for fields like launchDate - which are not required for indexing).

you will need to index launchDate, and use Lucene Sort: FullTextQuery setSort(Sort sort) (not on the Criteria).

It would be nice to find a solution to "remap" the Criteria Sort onto the Lucene one, but we still need the sorted field to be in the index.

Jaba · **Posted:** Fri Aug 14, 2009 5:27 am

Hi, s.grinovero. Thank you for your interest.
In case of this snippet

Code:

                final FullTextQuery ftQuery = fullTextSession.createFullTextQuery(luceneQuery, searchEntity);
                int begin = page * (int) itemsPerPage;
                Criteria criteria = session.createCriteria(searchEntity).setMaxResults(itemsPerPage).setFirstResult(begin);
                ...
                ftQuery.setCriteriaQuery(criteria);
                List<T> results = ftQuery.list();

Without ordering, I am getting this SQL query from hibernate (MySQL dialect used)

Code:

Hibernate: select ... where (this_.REALTY_MESSAGE_ID in (?, ?, ?, ?, ?, ?, ?)) limit ?

The paging of search results is working correct (e.g. when I am retrieving the next page LIMIT clause is changed to 5,10) and so forth.
As far as i understand, Lucene returns IDs in order by relevance (Lucene order) and list them in "in" clause of the query. So, i get the Lucene ordered result.

In the second case, with ordering, I am getting the query:

Code:

Hibernate: select ... where (this_.REALTY_MESSAGE_ID in (?, ?, ?, ?, ?, ?, ?)) order by this_.BEGIN_DATE asc limit ?

So, according to this query, i am getting the list of items which ids contained in the "IN" clause, then ordering is performed and then "LIMIT" clause truncated results. In this case I am getting results not ordered by Lucene, but included in Lucene index according to search query.
Then when I am retrieving next ordered page (e.g. ... order by this_.BEGIN_DATE asc limit 5, 10), I am getting the next page of results already ordered by BEGIN_DATE! Yes, these are not ordered by Lucene, but I am using it as alternative ordering of results by date, price, district and so.

The live example is the following.
The user is preforming search from the web page - Lucene ordered results are returned. User can paging these results.
Alternatively, user have links to order results by date, price and so on. Using these links, user is sorting result found by Lucene.

I have a full working result of this approach, but the project in development now. I am planing it in production at the end of the month. But this site will support only Russian language, to view working sample. By the way, the paging is working correct in my case.

sanne.grinovero · **Posted:** Fri Aug 14, 2009 12:36 pm

I'm sorry, this won't work correctly in most cases, as the keys used in the "IN (keys)" clause will not be all results but just the current Lucene page, so Lucene decided which N results you get; after that you'll order them by SQL but you have selected the wrong keys.

It might appear to work in simple situations, were your test results do fit in your Lucene page (and I don't remember right now if that matches the Criteria page, but might be bigger), especially if you test on a limited dataset.

Jaba · **Posted:** Mon Aug 17, 2009 6:54 am

Ok, you are "scared" me... :). I'll investigate it more deeply, especially in case (if that matches the Criteria page, but might be bigger), because I have got the successful results with nearly 25 items with 5 per page...

Alternatively,

Quote:

you will need to index launchDate

As far as I understand it should be @Field(index=Index.UN_TOKENIZED, store=Store.YES), as mentioned in the 4.1.2 in the hibernate_search.pdf. I am asking about "store" parameter. Should it be Store.YES?
In 4.1.1 of the search reference it is said:

Quote:

Whether or not you want to store the data depends on how you wish to use the index query result. For a regular
Hibernate Search usage, storing is not necessary. However you might want to store some fields to subsequently
project them (see Section 5.1.2.5, “Projection” for more information).

So, if I am not planning to use projection, but I want to use sorting, the "sort" parameter can be Store.NO, isn't it?

Thanks.

sanne.grinovero · **Posted:** Wed Aug 26, 2009 6:23 pm

Quote:

So, if I am not planning to use projection, but I want to use sorting, the "sort" parameter can be Store.NO, isn't it?

right, no need to STORE for Sorting only.