CODEWITHSUNDEEP
javaspring-data-jpajpql
Andrew
Andrew

Reputation: 722

Spring JPA Query Check If At Least One Element of a List Exists in Parameter List

I am trying to create a query using a Spring JPA repository. I have an Item with a list of Groups that are allowed to access the item. Given a User that belongs to one or more Groups, I want to query the Item table and return all of the Items where the User belongs to at least one of the Item's allowed Groups.

@Query("select item from Item item where item.allowedGroups.id in ?1")
Page<Object> findByAllowedGroups(List<Long> userGroupIds, Pageable pageable);

This is causing the following exception however:

org.hibernate.QueryException: illegal attempt to dereference collection [item0_.id.allowedGroups] with element property reference [id]

Ideally I would to do a JOIN on the item.allowedGroups and the userGroupIds parameter, but I have been unable to determine how to do a JOIN on a parameter to a JPA query.

Essentially, I need to know what the recommended Spring JPA query solution is for determining if at least one element of an object's list field exists in a given parameter.

Item Class:

@Entity
@Table(name = "item")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Item extends AbstractAuditingEntity implements Serializable,Comparable<File> {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToMany(fetch = FetchType.EAGER)
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@JoinTable(name = "item_allowed_groups",
    joinColumns = @JoinColumn(name="item_id"),
    inverseJoinColumns = @JoinColumn(name="group_id"))
private Set<Group> allowedGroups = Sets.newHashSet();

// getters and setters

Group Class:

@Entity
@Table(name = "group")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Group implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@NotNull
@Size(max = 50)
@Column(name = "name", length = 50, nullable = false)
private String name;

// getters and setters

Upvotes: 3

Views: 8549

Answers (1)

Andrew
Andrew

Reputation: 722

I solved the issue by setting up Spring JPA QueryDSL and implementing the solution in this Stack Overflow answer: https://stackoverflow.com/a/33455117/3285398. I modified the BooleanExpression to the following:

public static BooleanExpression hasItemAccess(List<Group> groups) {
    QItem item = QItem.item;
    return item.allowedGroups.any().in(groups);
}

And added a query method to the ItemRepository:

public interface ItemRepository extends JpaRepository<Item,Long>, QueryDslPredicateExecutor<Item> {

@Query("select item from Item item where item.user.login = ?#{principal.username}")
Page<Item> findByUserIsCurrentUser(Pageable pageable);

Page<Item> findAll(Predicate predicate, Pageable pageable);

}

Upvotes: 1

Related Questions