Query persistence Entity with join column using IN operator

Question

I have an entity such as following with a join over story_id with another entity called Story.

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "user_story_mapping")
public class UserStoryMapping {
 
     @ManyToOne
     @JoinColumn(name = "story_id")
     private Story story; // this attribute
 
     @Column(name = "user_id")
     private String userId;
 
     @Column(name = "seen_at")
     private Date seenAt;
  
     @Transient
     private String storyId;
}

What I am trying to perform is query this table such as

select usm.story_id as storyId from user_story_mapping usm where usm.user_id='Naman' and usm.story_id IN ("8a9f90858871dc9f018abc3fc3dc4e86","ffhy8a9f9abc3fc3dc4e09") and usm.status = 1;

In the attempt to achieve this with the help of javax.persistence.EntityManager#createQuery, I have the following query constant referenced:

public static final String GET_ACTIVE_USER_STORY_MAPPINGS = """
             from UserStoryMapping usm \
             where ((usm.userId = :userId) and (usm.story IN :storyIds) and (usm.status = 1))
             """;

On execution, this results in the following exception

…nested exception is java.lang.IllegalArgumentException:
Parameter value element [8a9f90858871dc9f018abc3fc3dc4e86] did not match expected type [entity.core.Story (n/a)]"

If I would change the query to

public static final String GET_ACTIVE_USER_STORY_MAPPINGS = """
             select usm.story_id as storyId \
             from UserStoryMapping usm \
             where ((usm.userId = :userId) and (usm.story_id IN :storyIds) and (usm.status = 1))
             """;

then I get stuck with

org.hibernate.QueryException: could not resolve property: story_id of: entity.core.UserStoryMapping [select usm.story_id as storyId\nfrom entity.core.UserStoryMapping usm where ((usm.userId = :userId) and (usm.story_id IN :storyIds) and (usm.status = 1))\n];

Answer 1

The error messages states the problem: the UserStoryMapping.story mapping is to an instance of Story, so you are required by JPA to pass in Story instances for query operations that use this mapping, but you are instead passing in the ID values directly. If you are going to be using the IDs, you would need to use a query expression of the form: "(usm.story.id in :storyIds)" instead, so that the query matches the ID values to the Story.Id columns. Many providers will perform the join to Story though, since by using usm.story, the specification requires inner join functionality on the results.

Some providers may allow passing in the Story instances to the query directly. For them, you can use "(usm.story IN :storyInstances)". It is up to the provider and the mapping type; they may convert this to "(usm.story.id in :storyIds)" by pulling out the IDs from the story instances you pass in, or they may use the value just to reference the FK in usm.

If you want to avoid the join and not rely on JPA provider behavior or mapping specifics, you can also map the foreign key directly in your entity as a basic mapping.

@Entity
@Table(name = "user_story_mapping")
public class UserStoryMapping {
 
  @ManyToOne
  @JoinColumn(name = "story_id")
  private Story story; //this controls the FK, and must always be set

  @Column(name = "story_id", insertable=false, updatable=false)
  private String storyId;
  ..
}

storyId will then be set in the entity when UserStoryMapping instances are read from the database, but the value will not be used to control the "story_id" foreign key column - that is still only controlled by the story ManyToOne property. This will allow you to use either the story property in queries to access the join, or the storyId property as any basic mapping when you want to use the value without the join, allowing expressions of the form:

(usm.storyId IN :storyIds)