[jira] [Created] (OPENJPA-2545) Joining Entites results in multiple occurences of tables in FROM clause

["Olivier Paquet (JIRA)" <ji...@apache.org>]

Olivier Paquet created OPENJPA-2545:
---------------------------------------

             Summary: Joining Entites results in multiple occurences of tables in FROM clause
                 Key: OPENJPA-2545
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2545
             Project: OpenJPA
          Issue Type: Question
          Components: criteria, jpa
    Affects Versions: 2.3.0
            Reporter: Olivier Paquet
            Priority: Blocker


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.

{code}
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();
{code}

{code}
SELECT ... 
FROM A t0, B t1, A t2 
WHERE (t2.STATUS = 'PROCESSED') AND t0.CLEARING_RUN_ID = t1.CLEARING_RUN_ID(+)
{code}

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



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)