0

I have a table with 2 foreign keys. I need to create a query that aggregates results that use both foreign keys.

i.e. Table A "b_id" is a foreign key to Table B "id", Table A "c_id" is a foreign key to Table C "id" 123 is the variable id i want to query

I can successfully find 1 result where id = 123 that joins to Table B as follows ...

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();

Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
predList.add(p1); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

... and I can successfully find 1 result where id = 123 that joins to Table C as follows ...

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();

Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinC.get("id"),123);
predList.add(p1); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

So I thought to get both results in one query I need to "or" the 2 predicates as below but it doesn't work?

EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);

List<Predicate> predList = new ArrayList<>();

Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
Predicate p2 = cb.equal(joinC.get("id"),123);
predList.add(cb.or(p1, p2)); 

cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);

What am I doing wrong here please? thanks ...

The SQL generated by this query when I apply the change suggested by Twister is (with superfluous fields omitted)

SELECT t1.my_id FROM e t3, d t2, a t1, c t0 WHERE (((t1.my_id = 123) OR (t3.my_id = 123)) AND (((t0.id = t1.id) AND (t2.id = t0.id)) AND (t3.id = t2._id)))
r.l.
  • 41
  • 1
  • 10
  • I should add that when A.b_id is populated with 123, A.c_id is null and when A.c_id is populated with 123, A.b_id is null – r.l. Sep 20 '18 at 12:49
  • I have generated the sql using the amended code suggested by Twister below. As explained in my other comment, i had over-simplified the example and there is actually 1 join to 1 table and 3 consecutive joins to the other table. – r.l. Sep 21 '18 at 09:51
  • SELECT t1.my_id FROM e t3, d t2, a t1, c t0 WHERE (((t1.my_id = 123) OR (t3.my_id = 123)) AND (((t0.id = t1.id) AND (t2.id = t0.id)) AND (t3.id = t2._id))) – r.l. Sep 21 '18 at 09:51
  • It seems to me that it is applying the "or" to the wrong fields. It is saying **( Join B OR Join C) AND Join D AND Join E** when i want **(Join B) OR (Join C AND Join D AND Join E)** – r.l. Sep 21 '18 at 09:55
  • I will try to give all the information needed without listing out the entire database. It is difficult in these comment boxes as they keep entering the comment when you hit enter unlike my original post and i am new on here so i am still learning – r.l. Sep 21 '18 at 10:13
  • 'TABLE A: Primary Key "id", Foreign Key "b_id" (maps to TABLE B primary key), Foreign Key "c_id" (maps to TABLE C primary key) #JoinColumn(name = "b_id", referencedColumnName = "id") #ManyToOne private B b_id; #JoinColumn(name = "c_id", referencedColumnName = "id") #ManyToOne private C c_id; TABLE B: Primary Key "id" TABLE C: Primary Key "id", Foreign Key "d_id" (maps to TABLE D primary key) #JoinColumn(name = "d_id", referencedColumnName = "id") #ManyToOne private D d_id;' – r.l. Sep 21 '18 at 10:35
  • TABLE D: Primary Key "id", Foreign Key "e_id" (maps to TABLE E primary key) #ManyToOne #PrimaryKeyJoinColumn(name = "e_id", referencedColumnName = "id") private E e_id; TABLE E: Primary Key "id", Field "my_id" – r.l. Sep 21 '18 at 10:35
  • CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(); Root rt = cq.from(A.class); List predList = new ArrayList<>(); predList.add(cb.equal(rt.get("b_id").get("id"), 123)); Join joinC = rt.join("c_id", JoinType.INNER); Join joinD = joinC.join("d_id", JoinType.INNER); Join joinE = joinD.join("e_id", JoinType.INNER); predList.add(cb.equal(joinE.get("my_id"), 123)); Query q = em.createQuery(cq.select(rt).where(cb.or(predList.toArray(new Predicate[0])))); – r.l. Sep 21 '18 at 10:36
  • There is an entry in TABLE A that has b_id = 123 and c_id is null There is another entry in TABLE A that has b_id = null and c_id = d_id = e_id and e.my_id = 123. I want to bring back both entries, but it will only bring back one. – r.l. Sep 21 '18 at 10:36
  • Apologies for the formatting, I have replaced the ampersand symbol with a hashtag because it thinks i want to mention someone, and i did try to put single quotes around it to show it was code (see first entry) but it just ignored it – r.l. Sep 21 '18 at 10:37
  • 1
    @r.l. Your variables are very confusing. Can you print the complete query in your question? Also, why are avoiding the Entity Structure? – Phenomenal One Sep 21 '18 at 10:54
  • Can you try this `predList.stream().toArray(Predicate[]::new)` ? – Phenomenal One Sep 21 '18 at 11:00
  • Sorry yes some of the variables in my query were not correct. I have tried your suggestion but it makes no difference. It still gives me SELECT t1.id, t1.b_id, t1.c_id FROM e t3, d t2, a t1, c t0 WHERE (((t1.b_id = 123) OR (t3.my_id = 123)) AND (((t0.id = t1.c_id) AND (t2.id = t0.d_id)) AND (t3.id = t2.e_id))) – r.l. Sep 21 '18 at 11:33
  • The OR is still in the wrong place. I am asking it to OR the two predicates and it is splitting the second predicate and applying the OR to the first predicate and the first part of the second predicate. – r.l. Sep 21 '18 at 11:35
  • hello?......... – r.l. Oct 01 '18 at 11:21
  • anybody???????? – r.l. Oct 17 '18 at 08:39
  • I'm still encountering this problem. CriteriaBuilder.or just does not work. How can you "or" two predicates - that both give results when executed individually - and end up with no results? It just doesn't make any sense. – r.l. Sep 24 '19 at 13:53

1 Answers1

0

The function where(Predicate... restrictions) of CriteriaQuery uses operator 'AND' for all restrictions.

If you want to use "or" for 2 predicates, you can try this:

cq.select(rt).where(cb.or(predList.toArray(new Predicate[0])));
Twister
  • 161
  • 4
  • Thank you for your reply. It didn't work, although instead of bringing back nothing, it brought back the results from one of the foreign keys. I was thinking maybe your answer should have said "new Predicate[1]" but that didn't make any difference. I think I may have over-simplified the example. The second foreign key is actually 3 joins, not 1, so there is one join a -> b and the other join is a -> c -> d -> e. Unfortunately each time i press enter in this box it finishes my post so i will continue in other box to post the code – r.l. Sep 21 '18 at 09:38
  • Join joinC= rt.join("id", JoinType.INNER); Join joinD = joinC.join("id", JoinType.INNER); Join joinE = joinD.join("id", JoinType.INNER); predList.add(cb.equal(joinE.get("myId"), 123)); – r.l. Sep 21 '18 at 09:45
  • Actually I cannot determine what is your context. What do you have (your tables and their relationship)? What do you want to query? – Twister Sep 23 '18 at 13:26
  • Hi. I have included all the table and relationship information in the comments on the original thread. My query is trying to aggregate where an id exists via one join but also via another (multiple) join. When i set up the "or" in the criteria query on the two joins the sql is wrong and sets the "or" to the first join and the first part of the second join instead of "or" on the first joing and all parts of the second join. – r.l. Sep 24 '18 at 08:55
  • Again i have detailed this in the comments on the original post. Am i putting the "or" in the wrong place or is the bug with criteria query? I can write what i need in native sql but i wanted to do it more efficiently with criteria query so i need to know if it is possible. thanks .... – r.l. Sep 24 '18 at 08:55
  • Do you understand the question now? – r.l. Oct 01 '18 at 11:21