13 Replies Latest reply on Jul 7, 2009 5:33 PM by Sylvain Catudal

    How To: RESTRICTIONS ?

    Prathamesh Gaddam Novice
      Dear Team,

      1. Is there a way to use RESTRICTIONS/EntityQuery with:

      a. Sub-query
      b. On collection
      c. How to use OR and group by? Its throwing exception

      Note: using Seam.2.1.2CR

      2. I need to know the feasible way we can use the RESTRICTIONS. Following is one workout, How is it working?

      ProjectRole has NRole so the

      public class ProjectRoleList extends EntityQuery<ProjectRole> {

            private static final String[] RESTRICTIONS = {
                        "projectRole.projects.id = #{projectRoleList.projectsId}",
                    "lower(NRole.roleName) like concat(lower(#{nRoleList.NRole.roleName}),'%')",
                    "lower(NRole.roleId) like concat(lower(#{nRoleList.NRole.roleId}),'%')"};

      .
      .
      }

      Generated Hibernate Query:

      select
              top 11 projectrol0_.ID as ID21_,
              projectrol0_.ROLE_ID as ROLE7_21_,
              projectrol0_.CREATED_BY as CREATED2_21_,
              projectrol0_.CREATED_DATE as CREATED3_21_,
              projectrol0_.MODIFIED_BY as MODIFIED4_21_,
              projectrol0_.MODIFIED_DATE as MODIFIED5_21_,
              projectrol0_.PROJECT_ID as PROJECT6_21_
          from
              PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
              PORTALNEW.dbo.N_ROLE nrole1_
          where
              projectrol0_.ROLE_ID=nrole1_.ID
              and (
                  lower(nrole1_.ROLE_ID) like (lower(?)+'%')
              )
      17:26:01,778 INFO  [STDOUT] Hibernate:
          select
              projects0_.ID as ID27_0_,
              projects0_.ACTIVE as ACTIVE27_0_,
              projects0_.CREATED_BY as CREATED3_27_0_,
              projects0_.CREATED_DATE as CREATED4_27_0_,
              projects0_.CUSTOMER_ID as CUSTOMER17_27_0_,
              projects0_.CUSTOMER_ACCOUNT_ID as CUSTOMER5_27_0_,
              projects0_.DESCRIPTION as DESCRIPT6_27_0_,
              projects0_.EFFORT as EFFORT27_0_,
              projects0_.END_DATE as END8_27_0_,
              projects0_.MODIFIED_BY as MODIFIED9_27_0_,
              projects0_.MODIFIED_DATE as MODIFIED10_27_0_,
              projects0_.NAME as NAME27_0_,
              projects0_.ORGANIZATION_ID as ORGANIZ16_27_0_,
              projects0_.PROJECT_ID as PROJECT12_27_0_,
              projects0_.TYPE as TYPE27_0_,
              projects0_.PROJECT_TYPE as PROJECT13_27_0_,
              projects0_.PARENT_ID as PARENT20_27_0_,
              projects0_.SHORT_NAME as SHORT14_27_0_,
              projects0_.START_DATE as START15_27_0_,
              projects0_.USER_ID as USER18_27_0_
          from
              PORTALNEW.dbo.PROJECTS projects0_
          where
              projects0_.ID=?
      17:26:01,794 INFO  [STDOUT] Hibernate:
          select
              nrole0_.ID as ID20_0_,
              nrole0_.ACTIVE as ACTIVE20_0_,
              nrole0_.ROLE_ID as ROLE3_20_0_,
              nrole0_.ROLE_NAME as ROLE4_20_0_
          from
              PORTALNEW.dbo.N_ROLE nrole0_
          where
              nrole0_.ID=?
      17:26:01,809 INFO  [STDOUT] Hibernate:
          select
              count(projectrol0_.ID) as col_0_0_
          from
              PORTALNEW.dbo.PROJECT_ROLE projectrol0_,
              PORTALNEW.dbo.N_ROLE nrole1_
          where
              projectrol0_.ROLE_ID=nrole1_.ID
              and (
                  lower(nrole1_.ROLE_ID) like (lower(?)+'%')
              )


      Thank you in advance!


        • 1. Re: How To: RESTRICTIONS ?
          Prathamesh Gaddam Novice

          I guess, something related @ https://jira.jboss.org/jira/browse/JBSEAM-3302.


          Please assist with my above queries.

          • 2. Re: How To: RESTRICTIONS ?
            Prathamesh Gaddam Novice
            And, for example how can the following query can be set using RESTRICTION/EntityQuery:

            String mainQuery =     "select i from WorkLog wl, Issues i"+
                                     " where wl.issues.id = i.id"+
                                     " and wl.id ="+
                                     "     ("+
                                     "       select max(id)"+
                                     "       from     WorkLog wl2"+
                                     "       where     wl2.issues.id = wl.issues.id"+
                                     "     ) ";
            • 3. Re: How To: RESTRICTIONS ?
              Sylvain Catudal Newbie

              Here is a sample of restrictions...


              private static final String EJBQL = "select patient from Patient patient";     
              
              private static final String[] RESTRICTIONS = {
                             "patient.patientId in (#{patientList.patientIds})",
                             "patient.patientId in ( select mrn.patient.patientId from PatientMrn mrn where mrn in (#{patientList.mrns})",
                             "patient.ramqNumber in (#{patientList.ramqNumbers})",
                             "lower(patient.firstName) like lower( concat(#{patientList.firstName},'%') )",
                             "lower(patient.lastName) like lower( concat(#{patientList.lastName},'%') )",
                             "patient.patientId in (select patient.patientId from Roster r inner join r.patients patient where r = #{patientList.roster})" };
              
              private String ramqsString;
              private String mrnsString;
              private String patientIdsParam;
              private String firstName;
              private String lastName;
              private Roster roster;
              



              Hoping this helps...

              • 4. Re: How To: RESTRICTIONS ?
                Sylvain Catudal Newbie

                Now here what your HQL might look like...


                select issue 
                from Issue issue
                where 
                  issue.id in 
                    (select max(issue.id) 
                     from WorkLog wl inner join wl.Issues issue 
                     group by wl)
                





                You don't use restrictions in this case because nothing is dynamic.

                • 5. Re: How To: RESTRICTIONS ?
                  Prathamesh Gaddam Novice
                  Thank you, Sylvain! It is vey helpful.

                  w.r.t the main post,
                  "
                  1. Is there a way to use RESTRICTIONS/EntityQuery with:

                  a. Sub-query
                  b. On collection
                  c. How to use OR and group by? Its throwing exception

                  Note: using Seam.2.1.2CR"

                  Please assist towards query "c. How to use OR and group by?".
                  • 6. Re: How To: RESTRICTIONS ?
                    Sylvain Catudal Newbie

                    You have to use the setGroupBy method available.  If you add the group by expression yourself at the end of your query, it will not work.


                    You can also set the restriction logic operator with the method 'setRestrictionLogicOperator(String operator )'.  From what I understand, you can't use both 'and' and 'or' in the same query with the EntityQuery. 


                    The EntityQuery class is not an end all be all, it's perfect for 95% of the time.  When your query is too complex, you have handle the dynamic part yourself.


                    Sylvain


                    • 7. Re: How To: RESTRICTIONS ?
                      Dan Keyes Newbie

                      Sylvain,


                      You suggested using the setRestrictionLogicOperator method.  This may be asking a little too much, but could you please give an example of how that method is used?  Based on the name, I think I have a use for it, but the Seam documentation provides no information.

                      • 8. Re: How To: RESTRICTIONS ?
                        Sylvain Catudal Newbie

                        The method accepts or or and.  If the value has not been set, and is used as default.


                        Sylvain

                        • 9. Re: How To: RESTRICTIONS ?
                          Prathamesh Gaddam Novice

                          Thank you Sylvain, for the clarifcation/clue!


                          I too agree with Dan Keyes, an example will make a difference. Even in google I didnt find any example/tutorial.


                          So how can I use setRestrictionLogicOperator?


                          • 10. Re: How To: RESTRICTIONS ?
                            Sylvain Catudal Newbie

                            By default, the and operator is used. I guess that if in the constructor of your class, you call setRestrictionLogicOperator(or), you will have the behavior you wish for.


                            Look at this example


                            package foo.bar.user;
                            
                            import org.jboss.seam.ScopeType;
                            import org.jboss.seam.annotations.Name;
                            import org.jboss.seam.framework.EntityQuery;
                            
                            import java.util.Arrays;
                            import java.util.List;
                            
                            @Name("userList")
                            public class UserList extends EntityQuery<User> {
                            
                                 private static final long serialVersionUID = 4490166929258261391L;
                            
                                 private static final String EJBQL = "select user from User user";
                            
                                 private static final String[] RESTRICTIONS = { 
                                      "lower(user.firstName) like concat( '%', lower(#{userList.user.firstName}), '%' )",
                                      "lower(user.firstName) like concat( '%', lower(#{userList.user.lastName}), '%' )" };
                            
                                 private User user = new User();
                            
                                 public UserList() {
                                      setEjbql(EJBQL);
                                      setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                                      setMaxResults(15);
                                      setOrderColumn("lastName");
                                      setRestrictionLogicOperator("or");
                                 }
                            
                                 public User getUser() {
                                      return user;
                                 }
                            }
                            



                            Using this class, if both a lastName and a firstName are set on the user property, all records that answer to one restriction on the other will be returned.


                            Hope that helps! ;)


                            Sylvain


                            ps: Note that this functionality is only available in Seam 2.1.2

                            • 11. Re: How To: RESTRICTIONS ?
                              Sylvain Catudal Newbie

                              Sorry, little error in post, here is what the restriction should have been:


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

                              • 12. Re: How To: RESTRICTIONS ?
                                Prathamesh Gaddam Novice

                                Sylvain Catudal wrote on Jul 06, 2009 19:35:


                                The method accepts or or and.  If the value has not been set, and is used as default.

                                Sylvain


                                I had assumed that using 'setRestrictionLogicOperator' we can use AND and/or OR within same RESTRICTION. However with your code snippet I guess I'm wrong. Am I?


                                Thank you Sylvain, for your patience.

                                • 13. Re: How To: RESTRICTIONS ?
                                  Sylvain Catudal Newbie

                                  You can't use both in the same call. Like I said earlier, it's good for 95% of situations.


                                  You could still have something like this:


                                       
                                  private static final String[] RESTRICTIONS = { 
                                      "user.sexeCode = 'M' and lower(user.firstName) like concat( '%', lower(#{userList.user.firstName}), '%' )",
                                      "user.sexeCode = 'M' and lower(user.lastName) like concat( '%', lower(#{userList.user.lastName}), '%' )" };
                                  



                                  With this, you should be able to figure out a way to obtain the result you want.  Worst case, you build the whole HQL with code without using the restrictions, set it and get your result.  This way, you will still benefit from the other functionalities offered by EntityQuery.


                                  Sylvain