EntityQuery with ManyToMany

[mgvarley]

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

[ztiringer]

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!

[joblini]

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

[mgvarley]

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

[ztiringer]

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 })

[ztiringer]

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

[mgvarley]

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

mark