8 Replies Latest reply on Jun 14, 2009 12:05 PM by Natalie Wilson

    JBoss Seam EnitityQuery and Restrictions and “or” operation in the query

    sofie soft Newbie

      Hello,


      I've got a seam-gen generated project which (naturally) uses EntityQuery.


      In my search operation I want to use the opeartion or betwen the condition.


      I want to write my guery:



      select m from message m where condition1 or condition2

      but the restrictions in seam is treated as “and” operation.


      Is it possible to make or between the clauses in the restriction?


      Can you help me ?


         

        • 1. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
          sofie soft Newbie

          sofie soft wrote on Nov 04, 2008 09:49:


          Hello,

          I've got a seam-gen generated project which (naturally) uses EntityQuery.

          In my search operation I want to use the opeartion or betwen the condition.

          I want to write my guery:

          select m from message m where condition1 or condition2

          but the restrictions in seam is treated as “and” operation.

          Is it possible to make or between the clauses in the restriction?

          Can you help me ?

           


          please ;
          can you help me


          I have probleme
          in my project


          please help me

          • 2. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
            Dan Allen Master

            You can stealthly get away with using an OR within the scope of a single restriction. The value expression in each restriction becomes a named parameter in the generated JPQL query. The name is derived by concatenating the string el with the 1-based index of the restriction. So the first restriction clause uses the parameter name el1 and the second el2. Here is how you would compare a value expression against two different fields.


            <framework:restrictions>
              <value>
                lower(golfer.lastName) like concat(lower(#{roundCriteria.golferName}),'%') or
                lower(golfer.firstName) like concat(lower(:el1),'%')
              </value>
              ...
            </framework:restrictions>



            Putting that trick aside, what we still need is a way to set the operator used to combine the individual restriction clauses. I am thinking we need to add a flag to the Query component. JIRA?

            • 3. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
              Natalie Wilson Newbie

              I've tried to apply this trick within the EntityQuery it's self see my code below:




              public class BusinessSearch extends EntityQuery<Company>
              {
                   private Log log = LogFactory.getLog(this.getClass());
                   
                   private static final long serialVersionUID = 2164088365294109661L;
                   private static final String EJBQL = "from Company c";     
                   private static final String ORDER = "name";
                   private static final Integer MAXRESULTS = 30;
              
                   private Float lat;
                   private Float lon;
                  
                   
                   private static final String[] RESTRICTIONS = 
                   { 
                        "description like concat('%','lower(#{exampleCompany.searchPhrase})','%') or socialReturns like concat('%',':el1','%') or website like concat('%',':el1','%') ",
                        "needsfinance = #{exampleCompany.needsfinanceFilter}",
                        "companyType = #{exampleCompany.companyType}",
                        "lower(replace(name,'The ','')) like lower(concat(#{exampleCompany.name},'%'))",
                        "sector = #{exampleCompany.sector}",
                        "c.member = #{exampleCompany.member}",
                        "#{exampleCompany.benefitFilter} member of c.benefits",
                        "#{exampleCompany.certificationFilter} member of c.certifications",
                        "approved = #{constants.booleanTrue}"          
                   };
              
                   public BusinessSearch() {
                        setEjbql(EJBQL);          
                        setOrder(ORDER);
                        setMaxResults(MAXRESULTS);
                        setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                        log.info(getEjbql());
                   }
              
              





              I'm getting the following error though:




              Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [el1]



              • 4. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
                Natalie Wilson Newbie

                Sorry for my incomplete entry above.


                I meant to also say..any help or thoughts appreciated.


                Thanks! Nat

                • 5. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
                  Leo van den berg Master

                  Bit of a side remark,


                  Be very carefull with OR constructs in your queries, or-constructs are not the fastest operations especially if you have a lot of entries in your DB. In case of a lot entries make sure you've indexed the columns used for searching or if you have more entries and like-constructs think of using Hibernate-Search


                  Leo




                  • 6. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
                    Natalie Wilson Newbie


                    Be very carefull with OR constructs in your queries, or-constructs are not the fastest operations especially if you have a lot of entries in your DB. In case of a lot entries make sure you've indexed the columns used for searching or if you have more entries and like-constructs think of using Hibernate-Search

                    Leo

                    I totally agree with you, I would rather avoid using OR, but I am trying to implement a keyword search over multiple fields in one object and have so far not been able to implement a good solution for this or find another example that would work. Any ideas you have on this front would be most welcome!


                    thanks!

                    • 7. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
                      Leo van den berg Master

                      Use an array inside your criteria




                      <value>
                         yourObject IN (#{not empty criteria.arrayOfObjects ? criteria.arrayOfObjects : null})
                      </value>
                      
                      





                      Has the same efficiency problem, but works better.



                      You can use a multiValue selection as input for the arrayOfObjects and it works nice.


                      Works like a charm !! Not invented this myself, but you can find it in the Seam in Action book.


                      Darn people, spent some money, it saves you a lot of time !!


                      Leo

                      • 8. Re: JBoss Seam EnitityQuery and Restrictions and “or” operation in the query
                        Natalie Wilson Newbie

                        I found the problem with my code after some deliberation. The error below was misleading me into thinking that you couldn't use the el1 reference on the same line as you declare it.


                        Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [el1]



                        However after some testing I have now found my issue to be a syntax mistake.


                        The corrected line of code is this:


                        "description like concat('%',lower(#{exampleCompany.searchPhrase}),'%') or socialReturns like concat('%',:el1,'%') or website like concat('%',':el1','%') ",



                        Basically, I had to remove the single quotes from around lower and :el1


                        I hope this helps someone else.