JPA countBy on column mapped with @OneToMany

Question

I'm trying to count items countaining a specific string in a @OneToMany Set<String> column.

To do so, in my JpaRepository<User, String> i can use .size() on the result of List<User> findByCompanyIds(String companyId);, it works like a charm.

But i was expecting to use long countByCompanyIds(String companyId); but it does not work and i get a queryException.

Do i have to make a custom query or am i doing it wrong with the count querydsl?

@Entity
@Table(name = "user")
public class User {
    @Id
    @Column(name = "id", nullable = false)
    private String id;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "companyId",
            joinColumns = @JoinColumn(name = "id"),
            indexes = {
                    @Index(name = "company_ids_index", columnList = "company_ids")
            }
    )
    @Column(name = "company_ids")
    private Set<String> companyIds;

    public User() {
        companyIds = new HashSet<>();
    }
}

public interface UserDao extends JpaRepository<User, String> {
    /* WORKS PERFECTLY */
    @EntityGraph(attributePaths = {"companyIds"})
    List<User> findByCompanyIds(String companyId);

    /* DOES NOT WORK */
    @EntityGraph(attributePaths = {"companyIds"})
    long countByCompanyIds(String companyId);

    /* DOES NOT WORK (was proposed Here by @Nick) */
    @EntityGraph(attributePaths = {"companyIds"})
    long countByCompanyIdsContaining(String companyId);
}

Exception trace:

 org.springframework.dao.InvalidDataAccessApiUsageException: 
 org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list 
 [FromElement{explicit,collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.test.User.companyIds,
 tableName={none},tableAlias=companyids1_,origin=user user0_,columns={,className=null}}] [select count(generatedAlias0) 
 from com.test.User as generatedAlias0 left join generatedAlias0.companyIds as generatedAlias1 where generatedAlias1=:param0]

Answer 1

The problem with the countByCompanyIdsContaining approach is that it treats the companyIds set as a single string, which doesn't work as expected. To achieve what you want you can use a JPQL query:

@Query("SELECT COUNT(u) FROM User u WHERE :companyId MEMBER OF u.companyIds")

However, if you prefer not to use a custom query, an alternative solution is to introduce an @Embeddable value object for the company ID. By doing so, you also align with best practices and ensure a more organized representation of your data.

Here's what a value object for a company ID might look like:

@Embeddable
public class CompanyId implements Serializable {

    private String value;

    public CompanyId() {
        // Hibernate requires default constructor
    }

    public CompanyId(String value) {
        this.value = value;
    }

    // Getter, equals and hashCode methods
}

Next, update the User entity to use the CompanyId type and specify how the column should be mapped using @AttributeOverride annotation:

@Entity
@Table(name = "user")
public class User {
    @Id
    @Column(name = "id", nullable = false)
    private String id;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "companyId",
            joinColumns = @JoinColumn(name = "id"),
            indexes = {
                    @Index(name = "company_ids_index", columnList = "company_ids")
            }
    )
    @AttributeOverride(name = "value", column = @Column(name = "company_ids"))
    private Set<CompanyId> companyIds;

    public User() {
        companyIds = new HashSet<>();
    }
}

Now you should be able to use query like long countByCompanyIdsContaining(CompanyId companyId)