7 Replies Latest reply on Aug 7, 2011 10:10 AM by saml

    Entity Query Logical Operator

    pogasu

      Hi all:
      I would like to implement a search functionality in my application. I used EntityQuery classes and Restrictions before and I didnt have problems until today because I only needed to use one Logical Operator(either OR or AND) but never both at the same time.
      The query should look like this:


      select playlist from Playlist playlist
      where ( playlist.channel_1 = #{playlistList.playlist.channel_1} OR playlist.channel_2 = #{playlistList.playlist.channel_2} OR
      playlist.channel_3 = #{playlistList.playlist.channel_3}) 
      AND
      ( playlist.language = #{playlistList.german} OR playlist.language = #{playlistList.french} OR 
      playlist.language = #{playlistList.spanish})
      AND
      playlist.account = #{playlistList.playlist.account} 



      The EJBQL and Restrictions are the following:



        

       private static final String EJBQL = "select playlist from Playlist playlist";
      
         private static final String[] RESTRICTIONS = { "playlist.account = #{playlistList.playlist.account}",
                           "playlist.channel_1 = #{playlistList.playlist.channel_1}",
                          "playlist.channel_2 = #{playlistList.playlist.channel_2}","playlist.channel_3=#playlistList.playlist.channel_3}", 
      "playlist.language = #{playlistList.german}","playlist.language = #{playlistList.french}",
       "playlist.language = #{playlistList.spanish}" };




      How could I do that??? Using both logical operators in the restrictions string?

        • 1. Re: Entity Query Logical Operator
          robsonfpp

          That's a very good question!, I'm facing the same problem.


          Anybody knows this one?


          Gustavo, have you found a solution?

          • 2. Re: Entity Query Logical Operator
            saml

            Is there an solution available? I've the same problem.



            • 3. Re: Entity Query Logical Operator
              tbryan

              I think that I have an EntityQuery subclass that can handle this case.  It's not fully tested, but when I get back to my machine later, I'll post it here.  Maybe you can help me test it?


              • 4. Re: Entity Query Logical Operator
                tbryan

                Try this.  Let me know whether you find any bugs.  I just wrote this class a couple of weeks ago.



                import java.io.Serializable;
                import java.util.ArrayList;
                import java.util.Collections;
                import java.util.List;
                import java.util.regex.Pattern;
                
                import org.jboss.seam.core.Expressions;
                import org.jboss.seam.core.Expressions.ValueExpression;
                import org.jboss.seam.framework.EntityQuery;
                import org.jboss.seam.framework.Query;
                import org.jboss.seam.persistence.QueryParser;
                
                /**
                 * An {@link EntityQuery} that supports two operations, like 
                 * <code>A and (B or C) and D and (E or F)</code>
                 * or vice versa. 
                 */
                @SuppressWarnings("unchecked")
                public class MultiOpQuery<E> extends EntityQuery<E> {
                
                     /** For the {@link Serializable} interface. */
                     private static final long serialVersionUID = 1L;
                
                     private static final Pattern WHERE_PATTERN = Pattern.compile("\\s(where)\\s",         Pattern.CASE_INSENSITIVE);
                     
                     // Because Query doesn't expose these fields
                     private String parsedEjbql;
                     private List<String> parsedRestrictions;
                     private List<ValueExpression> queryParameters;
                     private List<ValueExpression> restrictionParameters;
                
                     
                     /**
                      * The list of restrictions grouped for two different operators.  
                      * Within each group, the clauses will be joined by the 
                      * {@link #getInnerLogicOperator()}.  The groups are joined 
                      * together by the {@link #getOuterLogicOperator()}.  That is, 
                      * with a list of clauses like ( (A), (B, C), (D), (E, F) ) and  
                      * outer operator AND, the query will be <br/>
                      * (A) and (B or C) and (D) and (E or F).
                      * </p>
                      */
                     private List< List< ValueExpression > > restrictionGroups = new ArrayList< List<ValueExpression> >();
                     
                     /**
                      * We only override this method because {@link Query} does not 
                      * expose setters for its private queryParameters field.
                      */
                     @Override
                     protected List<ValueExpression> getQueryParameters() {
                          return queryParameters;
                     }
                      
                     /**
                      * We only override this method because {@link Query} does not 
                      * expose setters for its private restrictionParameters field.
                      */
                     @Override
                     protected List<ValueExpression> getRestrictionParameters() {
                          return restrictionParameters;
                     }
                     
                     /**
                      * We only override this method because {@link Query} does not 
                      * expose its private parsedEjbql field.
                      */
                     @Override
                     public void setEjbql(String ejbql) {
                          super.setEjbql(ejbql);
                          parsedEjbql = null;
                     }
                     
                     /**
                      * We only override this method because {@link Query} does not 
                      * expose setters for its private parsedRestrictions field.
                      */
                     @Override
                     public void setRestrictions(List<ValueExpression> restrictions) {
                          super.setRestrictions(restrictions);
                          parsedRestrictions = null;
                     }
                
                     /** 
                      * Returns the logical operator that joins the groups of restrictions 
                      * from {@link #restrictionGroups}.
                      * @return the outer logical operator (AND or OR)
                      */
                     protected String getOuterLogicOperator() {
                          return getRestrictionLogicOperator();
                     }
                
                     /**
                      * Returns the logical operator that joins the clauses in a single 
                      * group from {@link #restrictionGroups}.  This operator is either 
                      * AND or OR, and it's always the opposite of {@link #getOuterLogicOperator()}.
                      * @return the inner logical operator (AND or OR)
                      */
                     protected String getInnerLogicOperator() {
                          return getOuterLogicOperator() == "and" ? "or" : "and";
                     }
                
                     /**
                      * Add a new group of restriction clauses that should be joined 
                      * to each other with the {@link #getInnerLogicOperator()} and 
                      * joined (as a group) to the other groups with the 
                      * {@link #getOuterLogicOperator()}.
                      * 
                      * @param expressionStrings - list of restriction clauses
                      */
                     public void addInnerRestrictionGroupAsStrings(List<String> expressionStrings) {
                           Expressions expressions = new Expressions();
                           List<ValueExpression> restrictionVEs = new ArrayList<ValueExpression>(expressionStrings.size());
                           for (String expressionString : expressionStrings)
                           {
                              restrictionVEs.add(expressions.createValueExpression(expressionString));
                           }
                           addRestrictions(restrictionVEs);          
                     }
                     
                     /**
                      * Add a new group of restriction clauses that should be joined 
                      * to each other with the {@link #getInnerLogicOperator()} and 
                      * joined (as a group) to the other groups with the 
                      * {@link #getOuterLogicOperator()}.
                      * 
                      * @param expressionStrings - list of restriction clauses
                      */
                     public void addOuterRestrictions(List<String> expressionStrings) {
                          for (String aRestriction : expressionStrings) {
                               addInnerRestrictionGroupAsStrings(Collections.singletonList(aRestriction));
                          }
                     }
                
                     /**
                      * This method is {@link #addInnerRestrictionGroupAsStrings(List)} 
                      * but with ValueExperssions instead of Strings.  Developers will 
                      * probably want to use the other method, but this one is used 
                      * internally by the class.  
                      * <p>
                      * Developer note: we maintain the flat list of restrictions so that 
                      * the base class's methods require less modification.  For everything 
                      * except building the query string, we don't really need to know how 
                      * the different clauses are grouped.  
                      * </p>  
                      * @param restrictions - list of restriction clauses as ValueExpressions
                      */
                     public void addRestrictions(List<ValueExpression> restrictions) {
                          restrictionGroups.add(restrictions);
                          List<ValueExpression> flatRestrictions = getRestrictions();
                          flatRestrictions.addAll(restrictions);
                          setRestrictions(flatRestrictions);
                     }
                     
                     /**
                      * We only override this method because we need to override 
                      * {@link #getRenderedEjbql()}, but {@link Query} does not 
                      * expose some of its private fields that are set in this method.
                      * We override and re-implement the method exactly the same way, 
                      * using our own private fields that we can access later. 
                      */
                     @Override
                     protected void parseEjbql() {
                          if (parsedEjbql == null || parsedRestrictions == null) {
                               QueryParser qp = new QueryParser(getEjbql());
                               queryParameters = qp.getParameterValueBindings();
                               parsedEjbql = qp.getEjbql();
                
                               List<ValueExpression> restrictionFragments = getRestrictions();
                               parsedRestrictions = new ArrayList<String>(restrictionFragments.size());
                               restrictionParameters = new ArrayList<ValueExpression>(restrictionFragments.size());
                               for (ValueExpression restriction : restrictionFragments) {
                                    QueryParser rqp = new QueryParser(restriction.getExpressionString(), queryParameters.size() + restrictionParameters.size());
                                    if (rqp.getParameterValueBindings().size() != 1) {
                                         throw new IllegalArgumentException("there should be exactly one value binding in a restriction: " + restriction);
                                    }
                                    parsedRestrictions.add(rqp.getEjbql());
                                    restrictionParameters.addAll(rqp.getParameterValueBindings());
                               }
                
                          }
                     }
                
                     /**
                      * Override this method so that we can handle inner and outer 
                      * groups of restriction clauses with different logical operators.
                      */
                     @Override
                     protected String getRenderedEjbql() {
                          StringBuilder builder = new StringBuilder().append(parsedEjbql);
                          StringBuilder innerBuilder;
                          
                          int i = 0;
                          for (List< ValueExpression > inner : restrictionGroups) {
                               innerBuilder = new StringBuilder();
                               // Restriction groups helps us keep our inner and outer grouping right, 
                               // but we work from the flattened lists restrictionParameter and parsedRestrictions.
                               for (int j = 0; j < inner.size(); j++) {
                                    Object parameterValue = restrictionParameters.get(i).getValue();
                                    if (isRestrictionParameterSet(parameterValue)) {
                                         if (innerBuilder.length() > 0) {
                                              innerBuilder.append(" ").append(getInnerLogicOperator()).append(" ");
                                         }
                                         innerBuilder.append(parsedRestrictions.get(i));
                                    }
                                    i++;                    
                               }
                               if (innerBuilder.length() > 0) {
                                    if (WHERE_PATTERN.matcher(builder).find()) {
                                         builder.append(" ").append(getOuterLogicOperator()).append(" ");                         
                                    } else {
                                         builder.append(" where ");                         
                                    }
                                    builder.append("(").append(innerBuilder.toString()).append(")");
                               }
                          }
                          if (getGroupBy() != null) {
                               builder.append(" group by ").append(getGroupBy());
                          }
                
                          if (getOrder() != null) {
                               builder.append(" order by ").append(getOrder());
                          }
                
                          return builder.toString();
                     }     
                }
                



                • 5. Re: Entity Query Logical Operator
                  saml

                  Many thanks tom,


                  this is exactly I'am looking for...
                  As I'am new to SEAM, I would be glad, if you could provide a small code-snippet, how to use the class.


                  Thanks a lot!
                  Manfred

                  • 6. Re: Entity Query Logical Operator
                    tbryan

                    You'd use it just like EntityQuery, except for how you add the query restrictions. 


                    For your query, it would be something like


                        private static final String EJBQL = "select playlist from Playlist playlist";
                    
                        private static final String[] RESTRICTIONS = { "playlist.account = #{playlistList.playlist.account}" };
                        private static final String[] RESTRICTIONS_CHANNEL = { "playlist.channel_1 = #{playlistList.playlist.channel_1}", "playlist.channel_2 = #{playlistList.playlist.channel_2}","playlist.channel_3 = #{playlistList.playlist.channel_3}" };
                        private static final String[] RESITRICTIONS_LANG = {"playlist.language = #{playlistList.german}","playlist.language = #{playlistList.french}",
                     "playlist.language = #{playlistList.spanish}" };
                    



                    And then when you're adding the query restrictions, you just do


                        setEjbql(EJBQL);
                    
                        addOuterRestrictions(Arrays.asList(RESTRICTIONS));
                        addInnerRestrictionGroupAsStrings(Arrays.asList(RESTRICTIONS_CHANNEL));
                        addInnerRestrictionGroupAsStrings(Arrays.asList(RESTRICTIONS_LANG));
                    



                    In this case addOuterRestrictions wasn't necessary.  I could have used addInnerRestrictionGroupAsStrings there, too.  addOuterRestrictions(String[]) is primarily useful for adding a bunch of clauses that use the outer operator.  That is, if you had (A) and (B) and (C) and (D or E or F), then you could put A, B, and C in RESTRICTIONS and use addOuterRestrictions to add them all at once.  Otherwise, you would have to call addInnerRestrictionsGroupAsStrings 4 times: once for each group inside parentheses.  Look at the source and it'll be clear what I mean.


                    I'm not sure whether that's what you were looking for. 


                    BTW, are you sure that your query is right?  I would have expected something more like


                        private static final String[] RESTRICTIONS_CHANNEL = { "playlist.channel_1 = #{playlistList.playlist.channel}", "playlist.channel_2 = #{playlistList.playlist.channel}","playlist.channel_3 = #{playlistList.playlist.channel}" };
                    



                    or


                        private static final String[] RESTRICTIONS_CHANNEL = { "playlist.channel = #{playlistList.playlist.channel_1}", "playlist.channel = #{playlistList.playlist.channel_2}","playlist.channel = #{playlistList.playlist.channel_3}" };
                    



                    That is, the user-selected channel matches one of the three possible channels on playlist, or the one channel on playlist matches one of three possible user selections. 

                    • 7. Re: Entity Query Logical Operator
                      saml

                      Many thanks tom,


                      this was extremly helpfull for me - I appriciate your help very much!


                      Manfred