2

I need to construct an intersect-type query with the following entities (reduced down for clarity).

@Entity // and other @ stuff
public class Member {
    @Id
    private Long id;
    private String name;
    ...
}

@Entity
public class Program {
     @Id
     private Long id;
     private Long programName;
     private List<ProgramLevel> levels;
     ...
}

@Entity
public class ProgramLevel {
    @Id
    private Long id
    private String levelName;
}

A member can belong to one or more programs, and he always has his program level, connected by this:

public class Membership {
    @Id
    private Long id;
    private Long memberId;     // this is the member
    private Long programId;    // in which program is he
    private Long programLevel; // and on what level
    ...
}

Example: I have three programs, Math, English, Science. Each of them has some levels, like, MAth has algebra, geometry, English has literature, spelling and grammar, Science has experiments and theory.

Also, example user Joe would have Math:algebra, English: grammar programs and levels. Example user Max would maybe have English:literature. So, member can have multiple programs, but only one level per program.

Now I need to count or fetch all members that match a few programs and some levels in them. Example: I want all users that have Math:algebra or geometry, and English:literature or grammar and Science: theory.

I'm not really into JPA stuff, so I'm getting stuck.

In SQL, I'd do an intersect. How would I do it with JPA?

I have something like this:

HashMap<Long, List<ProgramLevel>> levels = new HashMap<Long, List<ProgramLevel>>();
// then I fetch the levels map.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);
Root<Membership> root = query.from(Membership.class);

// this is the part where I'm stuck.

// I figured I could try with multiple inner joins?
for(Map.Entry<Long, List<ProgramLevel>> curentLevel: levels.entrySet()) {
    // cb.equal(root.join ??? what comes here?
    // what what what?
    // root.get("programId") = currentLevel.getKey()
    // AND root.get("programLevelId") IN currentLevel.getValue()

}
...

How would I get this?

Besides doing this as multiple inner joins, I don't know if it can be done as INTERSECT (db is PostgreSQL if it matters)?

Also, I'm not sure where in that query I'd put the condition to only get me distinct memberships with these conditions.

As a bonus, I'll have to create an OR query. This one is where I must match ALL programs, the next one needs ANY program/level match for a member to be included there. But once I figure this one, I'll figure that one on my own, I hope.

EDIT: a possible (pseudo)SQL select would look like:

SELECT count(membership) FROM membership m
  WHERE (m.program == :program1 and m.programLevel in :programLevels1ArrayOfLevels)
INTERSECT
SELECT count(membership) FROM membership m
  WHERE (m.program == :program2 and m.programLevel in :programLevels2ArrayOfLevels)
INTERSECT...
... /* this would go on for as many (Program, List<ProgramLevel>) pairs I have on input. Which is variable).

Or something like this:

SELECT count(membership) FROM membership m
JOIN membership m1 ON (m1.program = :program1 AND m1.programLevel IN m1.programLevels1Aray)
JOIN membership m2 ON (m2.program = :program2 AND m2.programLevel IN m1.programLevels2Aray)
/* Continued to as many input pairs I have */
Zlatko
  • 18,936
  • 14
  • 70
  • 123
  • 2
    You'd usually do this by joining the same table multiple times with different aliases. JPA should offer a way to do that, but I've blissfully not had to use it or its Criteria API for about a year now, so I'm going to point you in roughly the right direction then flee screaming for the hills. – Craig Ringer Dec 13 '13 at 03:32
  • Hmm. And how would I go about naming those joins? Because I don't know how many of them are there. I don't know if it shows, but Java is my second language :) – Zlatko Dec 13 '13 at 12:38
  • Could you post a [fiddle](http://sqlfiddle.com/) or something about what you want in terms of SQL? Also how many possible joins would you need. If you are only varrying a list of values you can achieve something similar to [this](http://stackoverflow.com/questions/403336/getting-a-query-intersection-in-jpa) by looping the list of values and creating joins on demand with the Criteria API. That way you can also alias on demand (`q1`, `q2`, `q3`, `qn` for example). – Anthony Accioly Dec 13 '13 at 19:51
  • @AnthonyAccioly I've updated the question, added a bit of SQL there to show what I want. – Zlatko Dec 13 '13 at 22:48

1 Answers1

2
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = cb.createQuery(Tuple.class);
Root<Membership> root = query.from(Membership.class);

Collection<Long> queredPrograms = levels.keySet()
Predicate[] queryPredicates = new Predicate[queredPrograms.size()];
int i = 0;
for(Long programId : queredPrograms) {
    queryPredicates[i++] = cb.and(
        cb.equal(root.get("programId"), programId),
        root.get("programLevel").in(levels.get(programId))
    );
}
criteriaQuery.where(
    cb.and(
        cb.or(queryPredicates),
        root.get("programId").in(queredPrograms)
    )
);

criteriaQuery.groupBy(root.get("programId"));
criteriaQuery.select(cb.tuple(cb.count(cb.distinct(root.get("memberId"))), root.get("programId")));
TypedQuery<Tuple> countSelection = entityManager.createQuery(criteriaQuery);

SQL equivalent for criteria API will be like this, (will return unique user count by each programId)

SELECT 
      COUNT(DISTINCT memberId),
      programId
FROM membership
WHERE 
      programId in (:PROGRAMS_ID_LIST)
      AND (
           (programId = :PROGRAM_ID AND programLevel IN (:PROGRAM_LEVEL_LIST))
           ...
           OR (programId = :PROGRAM_ID_N AND programLevel IN (:PROGRAM_N_LEVEL_LIST))
      )
GROUP BY programId

If you don't want count by each programId just remove programId from group by and select clauses, and change OR to AND in where clause. I hope this will help you.

AntonioVelichko
  • 36
  • 1