2 Replies Latest reply on May 4, 2008 8:40 PM by sander

    Search and many to many relationship

    enda

      Hello,


      I use seam search EntityQuery.
      Old versions of hibernate allowed things like this (RESTRICTIONS):



      person.exam.contestSet.id



      which asks collection of contests for its ID. This was taken off, because it was wrong. In older hibernate it worked.


      What am I supposed to do now?


      I have tried to do this


      person.exam
      in ( select ex from Exam ex, Contest contest where contest in (ex.contestSet) and contest.id = #{contest.id})



      this is accepted by HQL, but the SQL is generated wrong:


         ....
                 where
                      exam1_.id=contestset3_.examId 
                      and contestset3_.contestId=contest4_.id 
                      and (
                          contest2_.id in (
                              .
                          )
                      ) 
                      and contest2_.id=?
              ) limit ?
      



      it contains . inside in.


      I got similar result for this query:


      person.exam in (select contest.examExecutionSet from Contest contest where contest.id = #{contest.id})




      I have hibernate.jar 3.2.6.GA and MySQL JDBC connector 5.0.8


      Is that hibernate problem or is my query wrong?
      Or seam add something to it.


      Thank you

        • 1. Re: Search and many to many relationship
          sander

          If i'm not mistaken you'd want to select a person based on whether or not this person is enroled in a certain exam contest set.


          This is a hibenate thingy, nothing to do with seam. Upon version 3.2.something you need to join everthing so it would be like this; which is the normal way you would do it in sql


           
          SELECT person FROM Person as person
          INNER JOIN exam as person_exam
          INNER JOIN contestSet as person_exam_contestSet 
          WHERE person_exam_contestSet.id = ..
          

          • 2. Re: Search and many to many relationship
            sander

            should ofcourse be


            INNER JOIN person.exam as person_exam
            etc