2 Replies Latest reply on Sep 3, 2009 4:57 PM by Phil Haigh

    do filtering by time range

    Dmitry Trunikov Newbie

      Hi ALL!
      The app generated by seam-gen allows filter list of entities.
      A descendant class of EntityQuery allows to define possible criteria to search like below:

      private static final String[] RESTRICTIONS = { 
                  "lower(user.email) like concat(lower(#{userList.user.email}),'%')",
                  "lower(user.uname) like concat(lower(#{userList.user.uname}),'%')" };

      As you can see these criteria are relative simple.
      In my app I would like to do search by time range (the entity has Date field).
      The question is: Is it possible to define such criteria with help of RESTRICTION member or such functionality is not supported by EntityQuery? If it is; how to do that?

      Any thoughts will be very appreciated.

        • 1. Re: do filtering by time range
          Leo van den berg Master

          You could create a criteria-bean which contains the fields which you want to use. Look in the Seam doc chpter 13 on how to do that. You can use without a problem Date ranges, and - with some additional getters -a time range.

          • 2. Re: do filtering by time range
            Phil Haigh Novice

            You can identify an example Entity:

            public class Expedition implements Serializable

            and then use it like this:

                 private static final String[] RESTRICTIONS = 
                      "e.startDate > #{exampleExpedition.startDate}",
                      "e.startDate < #{exampleExpedition.endDate}",

            You can also extend the Hibernate dialect to provide some date functions for you (example follows for InnoDB):

            import org.hibernate.Hibernate;
            import org.hibernate.dialect.MySQLInnoDBDialect;
            import org.hibernate.dialect.function.SQLFunctionTemplate;
            public class ExtendedInnoDBDialect extends MySQLInnoDBDialect 
                    public ExtendedInnoDBDialect() 
                       registerFunction( "date_sub_interval", new SQLFunctionTemplate( Hibernate.DATE, "date_sub(?1, INTERVAL ?2 ?3)" ) );
                       registerFunction( "date_add_interval", new SQLFunctionTemplate( Hibernate.DATE, "date_add(?1, INTERVAL ?2 ?3)" ) );           

            This will then let you use EL restrictions such as:

            dueDate<date_add_interval(#{constants.currentDate}, 1, WEEK)