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:




            @Name("expedition")
            @Role(name="exampleExpedition")
            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() 
                    {
                       super();
                       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)