Trouble with relating 2 entities

[daxxy]

I have 2 related entities:

OfficeView.java:

     private int officeId;
     private String state;
     private String type;
     private List<Devices> devices;

...skip getters and setters...

     @OneToMany(mappedBy="officeView")
     public List<Devices> getDevices() {
          return devices;
     }
     public void setDevices(List<Devices> devices) {
          this.devices = devices;     
     }


and Devices.java

     private Integer devId;
     private String devName;
     private String devSerialNum;
     private String devPlatform;
     private OfficeView officeView;

...skip getters and setters...

    @ManyToOne
     @JoinColumn(name="office_id")
     public OfficeView getOfficeView() {
          return officeView;
     }
     public void setOfficeView(OfficeView officeView) {
          this.officeView = officeView;
     }
     
Forgetting about the Devices entity for a moment, the following works great. When
the form input is entered in basicsearch.xhtml, I get the result I want, namely
a list of OfficeView's.

basicsearch.xhtml:

     <h:form>
          <h:outputText value="State" />
          <h:inputText value="#{officeViewList.officeView.state}" />
          <h:outputText value="Office type" />
          <h:inputText value="#{officeViewList.officeView.type}" />
     </h:form>
     
     
and here is OfficeViewList.java:

     @Name("officeViewList")
     public class OfficeViewList extends EntityQuery<OfficeView> {
     
          private static final String EJBQL = "select officeView from OfficeView officeView";
     
          private static final String[] RESTRICTIONS = {
                    "lower(officeView.state)=lower(#{officeViewList.officeView.state})",
                    "lower(officeView.type) like concat('%',lower(#{officeViewList.officeView.type}),'%')",};
     
          private OfficeView officeView = new OfficeView();
     
          public OfficeViewList() {
               setEjbql(EJBQL);
               setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
               setMaxResults(25);
          }
          
          public OfficeView getOfficeView() {
               return officeView;
          }
               
     }

But now I want to also use devices.devName as additional search criteria and I can't figure out how to do it.

I add this to the basicsearch.xhtml:

          <h:outputText value="Hostname" />
          <h:inputText value="#{devicesList.devices.devName}" />
          
And I change the RESTRICTIONS in officeViewList.java like this:

     private static final String[] RESTRICTIONS = {
               "lower(officeView.state)=lower(#{officeViewList.officeView.state})",
               "lower(officeView.type) like concat('%',lower(#{officeViewList.officeView.type}),'%')",
               "lower(devices.devName) like concat('%',lower(#{devicesList.devices.devName}),'%')",};

And it doesn't work. I get this error:

Caused by: java.lang.IllegalArgumentException:
org.hibernate.QueryException: illegal
attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}devices]
with element property reference [devName] [select officeView from dne.nmt.ond.OfficeView
officeView where lower(devices.devName) like concat('%',lower(:el18),'%')]
   
I've played around with this endlessly, but clearly I don't understand this well enough to figure it out.

How do I do this?

TDR

PS I purposely only included information I thought would be helpful.  Let me know if I need more detail

[gonorrhea]

I have never experienced that exception, however, check this out:

Queries that use a path expression across a collection have always been "unsupported". I believe it was in 3.2.3 (per http://lists.jboss.org/pipermail/hibernate-issues/2007-July/006157.html) that the syntax actually became "illegal". 

http://www.experts-exchange.com/Programming/Languages/Java/J2EE/Q_22817651.html

Perhaps, this is the culprit:

<h:inputText value="#{devicesList.devices.devName}" />

[ztiringer]

In officeViewList modify EJBQL:

select officeView from OfficeView officeView join fetch officeView.devices devices

And you should also have a private List<Devices> devices, with accessors (it's not clear whether you already have it)

[daxxy]

Adding a join to the EJBQL did the trick.
Thank you!
TDR

[anitha.nagani.raj.gmail.com]

Hello Tanya,

I'm having a similar problem. Can you please let me know how did you add the restriction and got that field on to search page?