Hi.
I have two tables that don't have a defined relation, but I'm still trying
to join them together in a single query, using criteria builder API.
This query work as I want it to - synchronizing the rows from both tables:
Root<E_Application> root = q.from(E_Application.class);
Root<E_Searcher> root2 = q.from(E_Searcher.class);
q.where(cb.equal(root.get(E_Application_.packageName),
root2.get(E_Searcher_.packageName)));
q.select(cb.sum(cb.literal(1)));
the query that comes out is: select sum(1) from application t0 cross join
searched t1 where (t0.package_name = t1.package_name);
However, if I add another join:
Root<E_Application> root = q.from(E_Application.class);
Root<E_Searcher> root2 = q.from(E_Searcher.class);
Join<E_Application, E_AppState> j1 =
root.join(E_Application_.publicAppState);
q.where(cb.equal(root.get(E_Application_.packageName),
root2.get(E_Searcher_.packageName)));
q.select(cb.sum(cb.literal(1)));
I get an extra cross join, and end up with a Cartesian product.
SELECT SUM(1) FROM application t0 CROSS JOIN application t1 CROSS JOIN
application_search t3 INNER JOIN application_state t2 ON
t1.PUBLICAPPSTATE_ID = t2.id WHERE (t0.package_name = t3.package_name)
Is there any way to prevent this (except for defining the proper
relationship between Application and Searcher)? Is this a proper
implementation of the spec? It's sort of weird that the resulting query
effectively has a root that I didn't explicitly request, and don't have
control over...
The database is postgres, if it matters.
Thank you
