3 Replies Latest reply on May 12, 2004 5:28 AM by sesques

    How to JOIN several Tables in Finder-Methods

    konstantinidis
    konstantinidis May 11, 2004 11:17 AM

      Hello,

      I would like to transform a SQL-Statement like the following to a finder- (or Selector-)Method:

      SELECT c.id FROM myC c
      JOIN myB b ON b.id_c = c.id
      JOIN myA a ON a.id_b= b.id
      JOIN myD d ON c.id_d = d.id
      WHERE a.myFieldFromA = 1
      AND c.myFieldFromC = 2
      AND d.myFieldFromD = 3

      The problem is that I want to get a Bean from within the hierarchy, not from the beginning.

      So
      SELECT Object (c) FROM myC c, IN(c.myDdetails) d WHERE ...
      would be okay for myC and myD, but what about the others?

      Do I have to write the finder-Method in the Bean at the bottom of the hierarchy although I want a Collection of Beans from a higher lvl? Would that work ?

      SELECT Object(c) FROM myA a, IN(a.myBdetails) AS b, IN(b.myCdetails) AS c, IN(c.myCdetails) AS d WHERE...
      (look at the Object(c))

      Thx for help in advance!

      Lachdanan

      • 3154 Views
      • Tags:
        • 1. Re: How to JOIN several Tables in Finder-Methods
          sesques
          sesques May 11, 2004 3:20 PM (in response to konstantinidis)

          Hi again,

          Wherever you implement your finder, it should work.

          The rule is:

          If the relation is a one side, you navigate in the WHERE clause A.B.C.fieldC

          If the relation is a many side, you navigate in the FROM clause like in your query
          SELECT Object( c ) FROM myA a, IN(a.myBdetails) AS b, IN(b.myCdetails) AS c, IN(c.myCdetails) AS d WHERE...

          I did not myself try to navigate in the FROM clause. I just test the deployment, it is OK: this means that the EJB-QL parser is OK too.
          Please, let me know if it works.

          Pascal

            • Actions
          • 2. Re: How to JOIN several Tables in Finder-Methods
            konstantinidis
            konstantinidis May 12, 2004 5:03 AM (in response to konstantinidis)

            Hello, Pascal,

            thx for your answer!

            Perhaps I didnt ask well. The problem is that I have a hierarchy like
            A M <--> 1 B M< --> 1 C M <--> 1 D
            (where A, B, C, D are the table names and M = many)
            and I want to get some Entities from C, but I have to make a JOIN on all tables A, B, C, D because decision what records are selected depends on some fields in A, B, C, D.

            So when I write a finder-method for Bean C can I write f.e.:
            SELECT OBJECT(c) FROM C c WHERE C.B.A.field = ? AND C.B.field = ?

            Would the parser accept that? Would I have to define some new Relations (from C to B, from B to A)?

            When I would need A-Entities it would be exactly what you wrote! But in this case I dont know.

            I did not myself try to navigate in the FROM clause. I just test the deployment, it is OK: this means that the EJB-QL parser is OK too.

            I dont understand that. I always write the finder-method, create the interfaces with XDoclet, deploy the beans to JBoss and restart JBoss to show me if it works. This is quite unproductive. Is there a better and faster way??

            Thx for your help!

            Lachdanan

              • Actions
            • 3. Re: How to JOIN several Tables in Finder-Methods
              sesques
              sesques May 12, 2004 5:28 AM (in response to konstantinidis)

               

              "konstantinidis" wrote:
              Perhaps I didnt ask well. The problem is that I have a hierarchy like
              A M <--> 1 B M< --> 1 C M <--> 1 D
              (where A, B, C, D are the table names and M = many)
              and I want to get some Entities from C, but I have to make a JOIN on all tables A, B, C, D because decision what records are selected depends on some fields in A, B, C, D.

              So when I write a finder-method for Bean C can I write f.e.:
              SELECT OBJECT(c) FROM C c WHERE C.B.A.field = ? AND C.B.field = ?

              Would the parser accept that? Would I have to define some new Relations (from C to B, from B to A)?

              Yes the query is OK (conform to the spec) and the parser is OK too.

              "konstantinidis" wrote:

              When I would need A-Entities it would be exactly what you wrote! But in this case I dont know.

              I don't know myself : (unspecified in the spec) but the parser is OK too.

              "konstantinidis" wrote:

              I did not myself try to navigate in the FROM clause. I just test the deployment, it is OK: this means that the EJB-QL parser is OK too.

              I dont understand that. I always write the finder-method, create the interfaces with XDoclet, deploy the beans to JBoss and restart JBoss to show me if it works. This is quite unproductive. Is there a better and faster way??

              I just mean that I have tried the syntax in my application (quickly) and redeploy to test. I had no errors, so I can say that the parser is OK. But I don't test the query and look at the results (because I'm not in such a configuration). If you do that, tell me if it works.
              For testing, I do the same way as you, just don't need to stop and restart JBoss, you can do a hot deployment while running.

              Pascal





                • Actions