Operand should contain 2 column(s) when fetching data through JPQL using IN & MEMBER OF clause

Question

I am trying to fetch data of employee availability creating a query. I have used @ElementCollection annotation to map skills and daysAvailable, which stores data into other table.

TypedQuery<Employee> query = entityManager.createQuery("select e from Employee e where :day MEMBER OF e.daysAvailable and :list MEMBER OF e.skills ", Employee.class);//
        query.setParameter("day", dayOfWeek);
        query.setParameter("list", employeeRequestDTO.getSkills());
        List<Employee> list = query.getResultList();

I am trying to pass a set in my query. If there is only one element in set then the query works but if more then 1, then it is not able to fetch. I have tried using e.skills in (:list) instead of :list MEMBER OF e.skills. But still no luck!!

@Entity
public class Employee {

    @Id
    @GeneratedValue
    private long id;

    @Nationalized
    private String name;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "skills", joinColumns = @JoinColumn(name = "emp_id"))
    @Enumerated(EnumType.STRING)
    private Set<EmployeeSkill> skills;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "days", joinColumns = @JoinColumn(name = "emp_id"))
    @Enumerated(EnumType.STRING)
    private Set<DayOfWeek> daysAvailable;

// Getters & Setters

This is the domain i have created and now want to fetch all employees who are available on a particular day and have particular skills. I have created query for this but it is showing error - java.sql.SQLException: Operand should contain 2 column(s)

This is the query generated-

select  * from employee e where ( 'TUESDAY' in ( select d.days_available from days d where e.id=d.emp_id)) and (( 'PETTING','FEEDING' ) in (select s.skills from skills s where e.id=s.emp_id));

Any suggestions?

Answer 1

In JPQL, MEMBER OF takes a single argument and checks if that's contained in the provided collection.

Check out the Hibernate User Guide for more details.

Try this query instead:

select e 
from Employee e 
join e.skills s
where 
    :day MEMBER OF e.daysAvailable  and
    s in :list