Set of string-tupels as parameter for IN

Hibernate ORM
1

Hi,

I have a Postgresql DB with the following structure:

CREATE TABLE projects (
  id SERIAL,
  title TEXT
);
CREATE TABLE labels (
  id SERIAL,
  projects_id INT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  key VARCHAR(127) NOT NULL,
  value TEXT NOT NULL,
  UNIQUE (projects_id, key)
);

I now want to find all projects which have all labels associated. SQL looks like:

select p.id
from projects p
join (
  select projects_id 
  from labels
  where (key, value) in (
    ('k1', 'v1'), -- The list of labels to look for
    ('k2', 'v2'),
    ('k3', 'v3'),
    ('k4', 'v4'),
    ('k5', 'v5')
  )
  having count(*) = 5 -- We want full count of them
  group by projects_id
) x on x.projects_id = p.id

The challenge now is how to specify the “list of labels” using hibernate (I use spring-boot 3.2, Hibernate 6.3.1) and try to use the @Query annotation.

@Query("""
select p.id
from projects p
join (
  select projects_id 
  from labels
  where (key, value) in (?)
  having count(*) = ?
  group by projects_id
) x on x.projects_id = p.id
""", nativeQuery = true)
List<Long> findByLabels(Map<String, String> labels, int size);

Now I always get:

operator does not exist: record = bytea
No operator matches the given name and argument types. You might need to add explicit type casts.
...

I tried to use `entrySet()` of the Map but that got me an exception like: `No JDBC mapping could be inferred for parameter`.

How may I specify these list/set of  String tuples?
2

That’s unfortunately not really possible, unless you use HQL and a specific embeddable type that wraps key and value. If your model looks like this:

@Entity
class Project {
    @Id 
    @GeneratedValue
    Integer id;
    String title;
    @OneToMany(mappedBy = "project")
    Set<LabelEntry> labels;
}

@Entity
class LabelEntry {
    @Id 
    @GeneratedValue
    Integer id;
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "projects_id")
    Project project;
    @Embedded
    Label label;
}

@Embeddable
class Label {
    String key;
    String value;
}

You can then query it like this with HQL:

@Query("""
select p.id
from Project p
where (
  select count(*) matches 
  from p.labels labelEntry
  where labelEntry.label in (?)
) = ?
""")
List<Long> findByLabels(Map<String, String> labels, int size);
1 Like
3

Thank you @beikov. I needed to convert the Map<String, String> to Set<Label> before I got this working and used lombok’s @Data annotation to add equality, hash and getters/setters automatically (not strictly needed but I had some tests which did require this) but now it works.