1 Reply Latest reply on Jun 21, 2010 12:15 PM by Tommaso Martini

    EntityQuery with restrictions on map property

    Tommaso Martini Newbie

      Hi all,


      I'm newbie to Seam and EJBQL so sorry if I'm asking a silly question, but I can't get out of this.


      I have an entity (Product) with a Map typed property (descriptions). How can I query this entity to retrieve only those products with a description like a specified one but only in a specified language? (key of the map).


      I mean, given the entity


      public class Product implements Serializable {
           private Map<Locale, String>     descriptions;
      }



      (the description field is mapped on a separate table on db with a mapkey varchar field for the key and a element varchar field for the description) and let's say instances are initialized somwhere like this:


      Product p1 = new Prodcut();
      p1.addDescription(Locale.ITALIAN, "abcde");
      p1.addDescription(Locale.ENGLISH, "abcde");
      Product p2 = new Prodcut();
      p2.addDescription(Locale.ITALIAN, "ghif");
      p2.addDescription(Locale.ENGLISH, "ghif");



      I'd want to retrieve only products with italian description like ab, so I wrote an EntotyQuery like this:


      @Name("productList")
      public class ProductList extends EntityQuery<Product> {
           private final static String EJBQL = "select p from Product p left join p.descriptions descr";
           private final static String[] RESTRICTIONS = {
                "lower(descr) like lower(#{productList.description})"
           }
      
           private String description;
      
           public ProductList() {
                setEjbql(EJBQL);
                setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
           }
      
           public String getDescription() {
                return description;
           }
      
           public void setDescription(String description) {
                this.description = description;
           }
      }



      but of course this finds both italian and english descriptions.


      What I don't know (and all my effeorts were unsuccessful) is how to impose a condition on the key of the dscriptions map.


      I tried using dereferencing directly the descr identifier in a restriction


      descr.mapkey



      tried using indices() function


      #{productList.description} in indices(descr)



      tried indexing descr identifier


      descr['it']



      but non of these worked.


      Any suggestion?
      Thanks

        • 1. Re: EntityQuery with restrictions on map property
          Tommaso Martini Newbie

          Finally I found a solution, I report here in case anyone has the same problem as mine.


          Simply don't use the map alias (descr in my example) but use directly the expression that produces the map. Referring to my case, this was the solution:


          @Name("productList")
          public class ProductList extends EntityQuery<Product> {
               private final static String EJBQL = "select p from Product p";
               private final static String[] RESTRICTIONS = {
                    "lower(p.descriptions[java.util.Locale.ITALIAN]) like lower(#{productList.description})"
               }
          
               private String description;
          
               public ProductList() {
                    setEjbql(EJBQL);
                    setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
               }
          
               public String getDescription() {
                    return description;
               }
          
               public void setDescription(String description) {
                    this.description = description;
               }
          }



          Using the expression that produces the map, you can use the indices() function instead of the indexing syntax and you can use also the value type stored on db instead of the object as filter value (i.e. 'it' instead of java.util.Locale.ITALIAN)