Expressing JOIN with dynamic VALUES in @Query

Question

The following query was taken from this answer. The only difference is that I need to match against a list of Enumeration values that contain possible attributes that I must filter. In addition the list of values will be dynamically set and may contain between 1 and 10 enumeration values.

select c.*
from comments c
join (
  values
    ("FOO",1),
    ("BAR",2),
    ("BAZ",3),
) as x (attribute, ordering) on c.attribute = x.attribute
order by x.ordering

How can I express this nested JOIN and the VALUE pairs in a Spring @Query method of a Spring Data repository? What other options are there to express this query in a Spring application?

I am open for solutions using both Hibernate, JPA or Spring Data (JPA).

Update I use Comment to demonstrate the issue, in practice I have a more complex entity. For the purpose of this question lets assume Comment is defined like this:

@Entity
class Comment {

   @Id @GeneratedValue Long id;
   String text;
   @Enumerated (value = EnumType.STRING)
   AttributeEnum attribute; //enum values: FOO, BAR, BAZ
}

Now, I want to select all comments where attribute is either 'BAR' or 'BAZ' and I want to specify at runtime that 'BAZ' comments should be ordered before 'BAR' comments.

I am looking to implement a function which behaves similar to the following:

Page<Comment> findCommentsByAttributeIn(List<AttributeEnum> attributes,
                                        Pageable pageable);

with the added constraint, that the sorting matches the order of attributes in the list. So if BAZ comes before BAR in the list it will be sorted before BAR.

Could you show a code of Comment entity? And what is the main aim of your issue, get comments with sorted nested attributes? — Cepr0, Mar 20 '18 at 10:52
"I want to specify at runtime that 'BAZ' comments should be ordered before 'BAR' comments" - as I understand, you are meaning ascending or descending ordering by `attribute`, like `findByAttributeInOrderByAttributeDesc` for example? Or you need an arbitrary order? — Cepr0, Mar 20 '18 at 13:32
What is the problem? Doesn't the query work? If so, what happens? Or don't you know where to put it? The title seems to suggest that you know that already? — Jens Schauder, Mar 21 '18 at 10:30
The problem is that my `attributes` list is dynamic, I might be getting three enum values or five. So I need to create the join table dynamically at run time. It seems that @Query only supports static queries, so I need some kind of alternative solution. — lanoxx, Mar 22 '18 at 07:36

Cepr0 · Answer 1 · 2018-03-20T14:22:27.883

I can offer the following solution:

First create predefine table with sorting order sets (all you need):

sorting_sets

attribute | ordering | set
------------------------------
FOO       |     1    |  1
BAR       |     2    |  1
BAZ       |     3    |  1
FOO       |     3    |  2
BAR       |     2    |  2
BAZ       |     1    |  2

Then you can use the following query:

@Query(value = "select c.* from comments c join sorting_sets ss on ss.attribute = c.attribute and ss.set = ?1 order by ss.ordering", nativeQuery = true)
List<Comment> getCommentsBySortingSet(int sortingSet);

to get sorting order related to the given set.

Expressing JOIN with dynamic VALUES in @Query

1 Answers1