Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2545

Joining Entites results in multiple occurences of tables in FROM clause

    XMLWordPrintableJSON

Details

    • Question
    • Status: Open
    • Blocker
    • Resolution: Unresolved
    • 2.3.0
    • None
    • criteria, jpa
    • None

    Description

      Hi,

      We use the Criteria API with OpenJPA to execute complex searches.
      But currently I'm blocked and as I found nothing on the web or your site I wanted to ask you directly:

      We have several Entites, let's say A, B, C

      Currenlty we do a LEFT JOIN between A and B to get all A Entities even if there is no relation to B, it's working fine as expected.

      Now we have another relation between B and C. B has just a String which represents the FK to C, so I wanted to join between B and C.

      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
      Root<A> clRun = query.from(A.class);
      Join<A, B> bJoin = clRun.join("clearingFiles",
      				JoinType.LEFT);
      
      query.where(cb.equal(clRun.get("status"), 
      ClearingRunStatus.PROCESSED));
      		query.select(cb.array(clRun, bJoin ));
      
      TypedQuery<Object[]> q = em.createQuery(query);
      List<Object[]> result = q.getResultList();
      
      SELECT ... 
      FROM A t0, B t1, A t2 
      WHERE (t2.STATUS = 'PROCESSED') AND t0.CLEARING_RUN_ID = t1.CLEARING_RUN_ID(+)
      

      So without the cb.equal, A is present 1 time -> OK
      But once I add an additiona comparison to one of the attributes from A, it add the Table a second time to the query, which generates a very big join and will crash our application.

      So please, is this a Bug or do I use it in a wrong way?

      Greetings, Olivier

      Attachments

        Activity

          People

            Unassigned Unassigned
            ollip89 Olivier Paquet
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: