@Formula 依靠 ManyToMany [英] @Formula count on ManyToMany

问题描述

我正在尝试在我的 Song 实体上添加一个 like count 字段，但由于我对 SQL 不是很精通，我不断收到语法错误.

I'm trying to add a like count field on my Song entity, but I keep getting syntax errors as I'm not very proficient in SQL.

我的喜欢系统可以工作，但我想在歌曲视图中显示计数.应该以歌曲ID在user_likes_song"表中出现的次数来统计.

My like system works, but I'd like to display the count in the songs view. It should count by how many times the song ID appears in "user_likes_song" table.

错误:SELECT"处或附近的语法错误位置:295

ERROR: syntax error at or near "SELECT" Position: 295

公式注释:

@Entity
@Data
@NoArgsConstructor
@Table(name = "song")
public class Song {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false, columnDefinition = "serial")
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "author", nullable = false)
    private String author;

    @Column(name = "content", nullable = false)
    private String content;

    @Formula("SELECT COUNT(i.id) FROM user_likes_song i WHERE song_id = i.id")
    private long likeCount;

    @ManyToOne
    @JoinColumn(name = "band_id", nullable = false)
    private Band band;

表格:

    CREATE TABLE IF NOT EXISTS user_acc (
      id SERIAL NOT NULL PRIMARY KEY,
      username text NOT NULL UNIQUE,
      password text NOT NULL,
      first_name text NULL,
      last_name text NULL,
      age INT NULL,
      phone text NULL,
      email text NOT NULL UNIQUE,
      status active_status NOT NULL DEFAULT 'active',
      create_date TIMESTAMP without TIME ZONE DEFAULT now() NOT NULL,
      update_date TIMESTAMP without TIME ZONE DEFAULT now() NOT NULL
    );

    -- -----------------------------------------------------
    -- Table song
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS song (
      id SERIAL NOT NULL PRIMARY KEY,
      name text NOT NULL,
      author text NULL,
      content text NOT NULL,
      status song_status NOT NULL DEFAULT 'inactive',
      create_date TIMESTAMP without TIME ZONE DEFAULT now() NOT NULL,
      update_date TIMESTAMP without TIME ZONE DEFAULT now() NOT NULL,
      band_id integer NOT NULL,
      user_id integer NOT NULL,
      CONSTRAINT fk_song_band
        FOREIGN KEY (band_id)
        REFERENCES band (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT fk_song_user1
        FOREIGN KEY (user_id)
        REFERENCES user_acc (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);

    -- -----------------------------------------------------
    -- Table user likes song
    -- -----------------------------------------------------

    CREATE TABLE IF NOT EXISTS user_likes_song (
      id SERIAL NOT NULL PRIMARY KEY,
      user_id integer NOT NULL,
      song_id integer NOT NULL,
      CONSTRAINT fk_user_likes_song_user1
        FOREIGN KEY (user_id)
        REFERENCES user_acc (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT fk_user_likes_song_song1
        FOREIGN KEY (song_id)
        REFERENCES song (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);

通过添加括号并将表格连接在一起来修复它:

Fixed it by adding parentheses and joining tables together:

@Formula("(SELECT COUNT(s.id) FROM user_acc u INNER JOIN user_likes_song us on u.id = us.user_id " +
            "INNER JOIN song s on us.song_id = s.id WHERE us.song_id = s.id )")
    private Long likeCount;

推荐答案

你的问题是由于hibernate @Formula 上的语法错误，所以让我深入解释一下，当你对公式进行查询时，在Hibernate 中添加了一个子查询，在查找歌曲时执行以下查询，例如:

Your issue is due to syntax error on hibernate @Formula, so let me explain in depth, when you put a query on the formula, in Hibernate is added a subquery, when finding a song is executed the following query for example:

选择 ID、姓名、作者、内容、(SELECT COUNT(i.id) FROM user_likes_song i WHERE song_id = i.id) 作为公式 FROM ....

SELECT id, name, author, content, (SELECT COUNT(i.id) FROM user_likes_song i WHERE song_id = i.id) as formula FROM ....

然后您需要在子查询中添加括号 ( ).

then you need to add parenthesis ( ) in your subquery.

@Formula("(SELECT COUNT(i.id) FROM user_likes_song i WHERE song_id = i.id)")
private long likeCount;

这篇关于@Formula 依靠 ManyToMany的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！