无法通过ID（主键）查询媒体，仅名称及别名可正常查询的问题

我需要编写查询语句，按分类、ID、名称及别名筛选MediaModel数据。当传入分类与名称/别名时，查询能正常返回结果；但仅传入分类与ID时，明明对应ID的媒体实体存在，查询却返回空列表。

问题查询语句 #

@Query("SELECT m FROM MediaModel m " +
        "LEFT JOIN AlternativeTitleModel a ON a MEMBER OF m.alternativeTitles " +
        "WHERE m.mediaCategory = :category " +
        "AND m.id = :id " +
        "OR m.name LIKE CONCAT('%',:name,'%') " +
        "OR a.name LIKE CONCAT('%',:alternativeTitle,'%')")
public List<MediaModel> findMediaByIdOrNameOrAlternativeTitleAndMediaCategory(
    @Param("id") Integer id,
    @Param("name") String name,
    @Param("alternativeTitle") String alternativeTitle,
    @Param("category") MediaCategoryModel category
);

简化查询同样报错 #

以下简化后的查询也返回空结果：

public Optional<MediaModel> findMediaByIdAndMediaCategory(Integer id, MediaCategoryModel category);

我已尝试移除左连接、别名及名称相关条件，但查询仍返回空列表。

相关实体类代码 #

MediaModel.java #

package com.espacogeek.geek.models;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

import org.hibernate.annotations.UpdateTimestamp;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import jakarta.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Table(name = "medias", indexes = {@Index(name = "idx_name_media", columnList = "name", unique = true)})
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class MediaModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_media")
    private Integer id;

    @Column(name = "name_media")
    private String name;

    @Column(name = "episode_count")
    private Integer totalEpisodes;

    @Column(name = "episode_length_in_minutes")
    private Integer episodeLength;

    @Column(name = "about", length = 1000)
    private String about;

    @Column(name = "url_cover")
    private String cover;

    @Column(name = "url_banner")
    private String banner;

    @JoinColumn(name = "id_category")
    private MediaCategoryModel mediaCategory;

    @OneToMany(mappedBy = "media", fetch = FetchType.EAGER)
    private List<ExternalReferenceModel> externalReference;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "medias_has_companies",
        joinColumns = @JoinColumn(name = "medias_id_media"),
        inverseJoinColumns = @JoinColumn(name = "companies_id_company"))
    private List<CompanyModel> company;
    
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "medias_has_people",
        joinColumns = @JoinColumn(name = "medias_id_media"),
        inverseJoinColumns = @JoinColumn(name = "people_id_person"))
    private List<PeopleModel> people;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "medias_has_genres",
        joinColumns = @JoinColumn(name = "medias_id_media"),
        inverseJoinColumns = @JoinColumn(name = "genres_id_genre"))
    private List<GenreModel> genre;

    @OneToMany(mappedBy = "media")
    @Transient
    private List<UserLibraryModel> userLibrary;

    @UpdateTimestamp
    @Column(name = "update_at")
    private Date updateAt;

    @OneToMany(mappedBy = "media", fetch = FetchType.EAGER)
    private List<AlternativeTitleModel> alternativeTitles;

    @OneToMany(mappedBy = "media", fetch = FetchType.EAGER)
    private List<SeasonModel> season;
}

AlternativeTitleModel.java #

package com.espacogeek.geek.models;

import java.io.Serializable;
import java.util.List;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Table(name = "alternative_titles", indexes = { @Index(name = "idx_name_alternative_title",columnList = "name", unique = true) })
@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class AlternativeTitleModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_alternative_title")
    private Integer id;

    @Column(name = "name_title")
    private String name;

    @ManyToOne
    @JoinColumn(name = "id_midia")
    private MediaModel media;
}

MediaCategoryModel.java #

package com.espacogeek.geek.models;

import java.io.Serializable;
import java.util.List;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import jakarta.persistence.Transient;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "media_categories")
public class MediaCategoryModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_media_category")
    private Integer id;

    @Column(name = "type_category")
    private String typeCategory;

    @OneToMany(mappedBy = "mediaCategory")
    @Transient
    private List<MediaModel> medias;
}

内容的提问来源于stack exchange，提问作者Vitor Hugo