2

I have the following class called "UserInterest". It follows the following definition:

@Table(name = "user_interests")
public class UserInterest {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_interest_pk_generator")
  @SequenceGenerator(name = "user_interest_pk_generator", sequenceName = "user_interest_id_seq", 
  allocationSize = 1)
  @Column(name = "user_interest_id")
  private int userInterestId;

  @Column(name = "user_id", nullable = false)
  private int userId;

  @ElementCollection
  @CollectionTable(name = "user_interest_ids")
  @Column(name = "interest_id")
  private List<Integer> interests;
}

JPA automatically creates the embedded table user_interest_ids, the collection table.

This class works fine, user interest is being saved correctly.

Now I need to filter the users based on particular interest list.

Let's say I have the following user_interest list:

enter image description here

And the CollectionTable looks like this:

enter image description here

I want to find the users who has interest id 3. In PostgreSQL's pgAdmin panel, the query to filter interest_id =3 return this table:

enter image description here

I tried to implement this filter query in Spring Data JPA, in native query by a inner join query:

@Query(value = "select user_id from user_interests inner join user_interest_ids on interest_id = ?1", nativeQuery = true)
Set<Integer> findUsersByInterest(int interestID);

This inner join query returns all the user, not only the users who has interest id 3.

I tried to find some helpful resources here in Stack overflow and other sources in internet, unfortunately I could not manage to find helpful resources for my problem.

Could you please give me some advices how to implement this query as native query inside Spring Data JPA or some links of other sources where I could find the helpful resource for my question.

I really appreciate your help.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
Mamun
  • 375
  • 2
  • 8
  • 17
  • you need a left outer join to return rows that do not have a joined entry in the join table. Research inner and outer joins. – K.Nicholas Jan 18 '21 at 22:49
  • Does this answer your question? [JPA JPQL: select items when attribute of item (list/set) contains another item](https://stackoverflow.com/questions/8339889/jpa-jpql-select-items-when-attribute-of-item-list-set-contains-another-item) – Jens Schauder Jan 19 '21 at 06:50
  • @K.Nicholas, thanks for your reply. I tried to user `left` or `left outer` join, but didn't work. The query follows such: `@Query(value = "select user_id from user_interests left outer join user_interest_ids on user_interest_ids.interest_id = ?1", nativeQuery = true)` - this query didn't work. Probably I need to figure out with join table. – Mamun Jan 19 '21 at 15:15
  • @JensSchauder, thank you very much for suggesting the link. I tried both answer from that thread, but it seems my native query does not recognize `MEMBER` or in the following query ` @Query(value = "select distinct user_id from user_interests d inner join d.user_interest_ids a where a.id = ?1", nativeQuery = true)`, the query shows an error for `where after a` saying `'(', CROSS, FULL, INNER, JOIN, LEFT, NATURAL, ON, RIGHT, TABLESAMPLE or USING expected, got 'where`. – Mamun Jan 19 '21 at 15:25
  • @JensSchauder, I checked [Spring Data JPA example](https://springframework.guru/spring-data-jpa-query/#:~:text=With%20%40Query%20Spring%20Data%20JPA,query%20and%20map%20the%20result.) to use JPQL and Native Query. It seems there is not difference regarding JPQL or Native Query when we write SQL command with `@query` tag in Spring Data JAP. I don't understand why in my `@query` it causes error. – Mamun Jan 19 '21 at 15:29
  • Well, I agree with looking at the join. I admit I didn't attempt to debug the example though you gave good detail. It's just that when I see "didn't return one of the rows" my goto is always sorting out the outer join thing. Basically it seems to me you need open up a query tool and work on making a sql query give you what you want so you properly understand the issue and the sql. – K.Nicholas Jan 19 '21 at 16:04

0 Answers0