6 Replies Latest reply on Apr 29, 2009 5:27 PM by Mark Varley

    EntityQuery with ManyToMany

    Mark Varley Newbie

      Hi all - I am trying to design a query in my application based on the standard EntityQuery as generated by seam-gen where one of the criteria is a many-to-many relationship (Vendor m:m Language).  The actual user requirement is to show all Vendors which speak a given Language.  The user can specify which languages to search for using a rich:pickList but since this is one of a number of criteria for searching vendors it is optional.  The problem is I don't know how to specify a restriction in the EntityQuery to model this.  I have tried a number of different options but can't get this to work.  I can do this with some messy hand-coded SQL (i.e. build up the query dynamically using a StringBuilder checking against the Vendor object to see what criteria have been specified) but am sure there is a cleaner way of doing this with EntityQuery and some nice HQL.  Any tips would be very much appreciated!


      Thanks,


      mark



        • 1. Re: EntityQuery with ManyToMany
          Zoltan Tiringer Newbie

          Just my 2 cents. I think you could save yourself a lot of pain by breaking down this M:N relationship into two 1:N relations with a VendorLanguage table in the middle. It would be a lot easier!

          • 2. Re: EntityQuery with ManyToMany
            Ingo Jobling Master

            Hi, your best bet may be to extend Entity Query to use the Hibernate Criteria API. 

            • 3. Re: EntityQuery with ManyToMany
              Mark Varley Newbie

              Thanks for the tips guys.  Ingo - unfortunately I am using the EJB entityManager so I don't think I have the option to run Criteria queries.  Regarding of 1:N or M:N I think my problem is the same i.e. how to pass multiple criteria from a selectMany... control into an EntityQuery restriction and for this to be optional.  I have created a LanguageListConverter class which is working well and the following query:


              private static final String EJBQL = "select distinct(vendor) from Vendor vendor left join vendor.languages language";



              But if I put the following restriction it just calls the toString() method on the list of languages and throws an error:


              private static final String[] RESTRICTIONS = { "language.id in  #{vendorList.vendor.languages})",};



              If there is a way of doing this in EJBQL I would be very grateful of any suggestions.  In the meantime I am using the following which works fine but not sure how it will perform in a production environment:


              @Override
              public String getEjbql() {
                   StringBuilder sb = new StringBuilder();
                   sb.append(EJBQL);
                   if (!vendor.getLanguages().isEmpty()) {
                        sb.append(" AND language.id IN (");
                        boolean first = true;
                        for (Language language : vendor.getLanguages()) {
                             if (!first)
                                  sb.append(",");
                             sb.append(language.getId());
                             first = false;
                        }
                        sb.append(")");
                   }
                   return sb.toString();
              }



              As always any help would be very much appreciated.


              Thanks,


              mark



              • 4. Re: EntityQuery with ManyToMany
                Zoltan Tiringer Newbie

                Define a private List<Language> in vendorList (with public accessors) and refer that in the restrictions. Something like:


                language.id IN (#{not empty vendorList.languages ? vendorList.languages : null })


                • 5. Re: EntityQuery with ManyToMany
                  Zoltan Tiringer Newbie

                  And of course, don't forget to modify the UI bindings, to bind to the new list...

                  • 6. Re: EntityQuery with ManyToMany
                    Mark Varley Newbie

                    Sorry for the slow reply Zoltan, only just saw your repsonse - tried it and it worked perfectly - thanks!


                    mark