[OPENJPA-2545] Joining Entites results in multiple occurences of tables in FROM clause

• Type: Question
• Status: Open
• Priority: Blocker
• Resolution: Unresolved
• Affects Version/s: 2.3.0
• Component/s: criteria, jpa
• Assignee: Unassigned
• Reporter: Olivier Paquet
• Created: 17/Nov/14 13:34
• Updated: 17/Nov/14 13:34

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