Criteria API : finding entities in a tree with inherited user permissions

Question

I'm trying to use the criteria API to perform a rather complex search in a hierarchical structure of locations.

I'm only writing here the relevant entities and their relevant attributes

Entities

Location
- Location upperLocation (may be null)
- Set<Location> childrenLocation (may be empty)
- Set<User> managers (may be empty)

Alert
- Location originatedIn (may be null)

User

More details

The relation between User and Location is ManyToMany.

The locations representation is hierarchical. For example World is a location which contains the countries USA, England, France, which themselves contains cities, ...

There can be unlimited levels of sub locations in the trees.

The alerts are originated in a Location or not.

A user is considered manager of a location if he his effectively (in the database) manager of it or if he is manager of one of the location's parents in the tree. Basically if you are manager of USA, you are automatically considered manager of all the children locations in USA, and their children too, etc ...

The criteria I'm trying to build has to find alerts that are originated in a location for which the user is either a direct manager or an inherited manager.

Code

I have a DAO :
AlertRepository extends JpaRepository<Alert, Long>, JpaSpecificationExecutor<Alert>
on which I request the query, just passing the specifications.

List<Alert> alerts = dao.findAll(buildSpecificationForUser(user))

And the specification builder :

private Specification<Alert> buildSpecificationForUser(final User user) {
        return new Specification<Alert>() {
            @Override
            public Predicate toPredicate(Root<Alert> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

                query.distinct(true);

                Expression<Collection<User>> managersOfLocations = root.get("originatedIn").get("managers");
                return builder.isMember(user, managersOfLocations);
            }

        };

    }

With this function I only get the alerts of the locations for which the user is directly a manager of.

Question

How to make it so that it would find also the alerts in the locations for which the user is an inherited manager ?

Update

I've tried also this :

Join<Alert, User> managersOfLocation = root.join("originatedIn").join("upperLocation",JoinType.LEFT).join("managers",JoinType.LEFT);
return builder.equal(managersOfLocation.get("id"),user.getId());

But the result set return all the alerts event if the user is not manager in any of the locations

There is a similar question but it hasn't been answered, no luck : http://stackoverflow.com/questions/26669841/java-criteria-api-join-self-referencing-entity?rq=1 — singe3, Jul 08 '15 at 14:16

score 1 · Accepted Answer · edited May 23 '17 at 12:06

Since the representation of Location is hierarchical, you will need a hierarchical query to fetch the data that you need. However, there is no direct hierarchical query support in JPA 2.1. If you expect to have a lot of Locations, you'll have to update your data model to use materialized paths or nested sets.

If you're not going to have a huge number of Locations, or modifying your table structure is not an option right now, you could create a view that does the hierarchical SQL for you, and map to that. For example:

If you create the following view manager_location_all (tested on PostgreSQL 9.4.4):

CREATE OR REPLACE VIEW manager_location_all AS
 WITH RECURSIVE ancestor_descendant(ancestor_id, descendant_id) AS (
         SELECT root.upper_location_id, root.location_id
           FROM location root
         UNION ALL
         SELECT l.upper_location_id, ad.descendant_id
           FROM location l
           JOIN ancestor_descendant ad ON l.location_id = ad.ancestor_id
        )
 SELECT manager_location.manager_id, ancestor_descendant.descendant_id AS location_id
   FROM ancestor_descendant
   JOIN manager_location ON ancestor_descendant.ancestor_id = manager_location.location_id
  UNION ALL
 SELECT manager_location.manager_id, manager_location.location_id
   FROM manager_location;

You could then add a Set<User> allManagers mapping to your Location object using that view then do root.get("originatedIn").get("allManagers") in your specification builder. This approach is not going to be as scalable as updating your data model, however.

Very complete answer I'll look into that. Thank you. – singe3 Jul 14 '15 at 23:16 — singe3, Jul 14 '15 at 23:16

Criteria API : finding entities in a tree with inherited user permissions

1 Answers1