如何在Sequelize中将此模型转换为物化视图?

ozxc1zmp  于 2022-10-15  发布在  PostgreSQL
关注(0)|答案(1)|浏览(137)

bounty 4天后到期。这个问题的答案有资格获得+50的声誉奖励。PirateApp正在寻找一个规范的答案

我有一个模型,它目前创建了一个表。相反,我想把这变成一个物化的观点。我怎么能这样做呢?

/* eslint import/no-cycle: "off" */
import {
  Model,
  PrimaryKey,
  Column,
  Table,
  Min,
  AllowNull,
  AutoIncrement,
  BelongsTo,
} from 'sequelize-typescript';
import { FeedItem } from 'data/models';

@Table({
  freezeTableName: true,
})
export default class FeedItemLikesDislikesAggregate extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column
  feedItemLikesDislikesAggregateId: number;

  @AllowNull(false)
  @Min(0)
  @Column
  dislikes: number;

  @AllowNull(false)
  @Min(0)
  @Column
  likes: number;

  @BelongsTo(() => FeedItem, {
    foreignKey: { name: 'feedItemId', allowNull: false },
    as: 'feedItemId_',
  })
  feedItemId: FeedItem;
}
ibrsph3r

ibrsph3r1#

我已经用sequelize.query完成了
首先,您需要创建示例化视图

// somewhere along your migrations/sync method

sequelize.query (`CREATE MATERIALIZED VIEW IF NOT EXISTS FeedItemLikesDislikesAggregateMV  AS
               SELECT   whatever, you, want, to
               FROM     FeedItemLikesDislikesAggregate
               GROUP BY 1;`);

我想您可以为它创建一个没有字段的模型,并像上面的类https://sequelize.org/docs/v6/other-topics/legacy/一样使用它作为一个普通的续集模型

FeedItemLikesDislikesAggregateMV.findAll();

然而,我在这里再次使用了sequelize.query

export const FILDAMV = sequelize.query(`SELECT * FROM FeedItemLikesDislikesAggregateMV`);

您可以随时通过执行以下操作来刷新它:

sequelize.query(`REFRESH MATERIALIZED VIEW unique_words;`);

如果这听起来太复杂/有问题,你可以使用这个库
https://www.npmjs.com/package/sequelize-mv-support
我自己还没用过呢。但如果它适合你,那就很好。祝好运。

相关问题