TypeScript Sequelize.literal函数代码示例

  /**
   * Gets block rewards for a delegate for time period
   */
  // tslint:disable-next-line max-line-length
  public async aggregateBlockReward(filter: { generatorPublicKey: publicKey, start?: number, end?: number }): Promise<{ fees: number, rewards: number, count: number }> {
    const params: any                                                         = {};
    params.generatorPublicKey                                                 = filter.generatorPublicKey;
    params.delegates                                                          = this.constants.activeDelegates;
    const timestampClausole: { timestamp?: any } = {timestamp: {}};

    if (typeof(filter.start) !== 'undefined') {
      timestampClausole.timestamp[Op.gte] = filter.start - this.constants.epochTime.getTime() / 1000;
    }

    if (typeof(filter.end) !== 'undefined') {
      timestampClausole.timestamp[Op.lte] = filter.end - this.constants.epochTime.getTime() / 1000;
    }

    if (typeof(timestampClausole.timestamp[Op.gte]) === 'undefined'
      && typeof(timestampClausole.timestamp[Op.lte]) === 'undefined') {
      delete timestampClausole.timestamp;
    }

    const bufPublicKey = Buffer.from(params.generatorPublicKey, 'hex');
    const acc          = await AccountsModel
      .findOne({where: {isDelegate: 1, publicKey: bufPublicKey}});
    if (acc === null) {
      throw new Error('Account not found or is not a delegate');
    }

    const res: { count: string, rewards: string } = await this.BlocksModel.findOne({
      attributes: [
        sequelize.literal('COUNT(1)'),
        sequelize.literal('SUM("reward") as rewards'),
      ],
      raw       : true,
      where     : {
        ...timestampClausole,
        generatorPublicKey: bufPublicKey,
      },
    }) as any;

    const data = {
      count  : parseInt(res.count, 10),
      fees   : (await this.RoundsFeesModel.aggregate('fees', 'sum', {
        where: {
          ...timestampClausole,
          publicKey: bufPublicKey,
        },
      })) as number,
      rewards: res.rewards === null ? 0 : parseInt(res.rewards, 10),
    };
    if (isNaN(data.fees)) {
      // see https://github.com/sequelize/sequelize/issues/6299
      data.fees = 0;
    }
    return data;
  }

 /**
  * Updates accounts and add a missing block to whoever skipped one
  * @returns {Promise<void>}
  */
 public updateMissedBlocks(): DBOp<any> {
   if (this.scope.roundOutsiders.length === 0) {
     return null;
   }
   return {
     model  : this.scope.models.AccountsModel,
     options: {
       where: {
         address: { [Op.in]: this.scope.roundOutsiders },
       },
     },
     type   : 'update',
     values : {
       // tslint:disable-next-line max-line-length
       cmb          : this.scope.dposV2 ? sequelize.literal(`cmb ${this.scope.backwards ? '-' : '+'} 1`) : 0,
       missedblocks : sequelize.literal(`missedblocks ${this.scope.backwards ? '-' : '+'} 1`),
     },
   };
 }

 function logAccess(user: string) {
     // User already known, log this access
     models.log_access.update({
         last_seen : new Date(),
         access_counter : sequelize.literal("access_counter + 1")
     }, {
         where : {
             user : user,
         }
     }).then(function(r: any) {
     }, function(error: any) {
         console.log(error);
     });
 };

  static listThreadParentComments (comment: VideoCommentModel, t: Sequelize.Transaction, order: 'ASC' | 'DESC' = 'ASC') {
    const query = {
      order: [ [ 'createdAt', order ] ],
      where: {
        id: {
          [ Sequelize.Op.in ]: Sequelize.literal('(' +
            'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
            'SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ' + comment.id + ' UNION ' +
            'SELECT p.id, p."inReplyToCommentId" from "videoComment" p ' +
            'INNER JOIN children c ON c."inReplyToCommentId" = p.id) ' +
            'SELECT id FROM children' +
          ')'),
          [ Sequelize.Op.ne ]: comment.id
        }
      },
      transaction: t
    }

    return VideoCommentModel
      .scope([ ScopeNames.WITH_ACCOUNT ])
      .findAll(query)
  }

const buildArrayArgAttribute = function (table: string): any {
  return [sequelize.literal(`(SELECT ARRAY_AGG("dependentId") FROM mem_accounts2${table} WHERE "accountId" = "AccountsModel"."address")`), table];
};

  static listAllAndSharedByActorForOutbox (actorId: number, start: number, count: number) {
    function getRawQuery (select: string) {
      const queryVideo = 'SELECT ' + select + ' FROM "video" AS "Video" ' +
        'INNER JOIN "videoChannel" AS "VideoChannel" ON "VideoChannel"."id" = "Video"."channelId" ' +
        'INNER JOIN "account" AS "Account" ON "Account"."id" = "VideoChannel"."accountId" ' +
        'WHERE "Account"."actorId" = ' + actorId
      const queryVideoShare = 'SELECT ' + select + ' FROM "videoShare" AS "VideoShare" ' +
        'INNER JOIN "video" AS "Video" ON "Video"."id" = "VideoShare"."videoId" ' +
        'WHERE "VideoShare"."actorId" = ' + actorId

      return `(${queryVideo}) UNION (${queryVideoShare})`
    }

    const rawQuery = getRawQuery('"Video"."id"')
    const rawCountQuery = getRawQuery('COUNT("Video"."id") as "total"')

    const query = {
      distinct: true,
      offset: start,
      limit: count,
      order: getSort('createdAt', [ 'Tags', 'name', 'ASC' ]),
      where: {
        id: {
          [Sequelize.Op.in]: Sequelize.literal('(' + rawQuery + ')')
        },
        [Sequelize.Op.or]: [
          { privacy: VideoPrivacy.PUBLIC },
          { privacy: VideoPrivacy.UNLISTED }
        ]
      },
      include: [
        {
          attributes: [ 'id', 'url' ],
          model: VideoShareModel.unscoped(),
          required: false,
          // We only want videos shared by this actor
          where: {
            [Sequelize.Op.and]: [
              {
                id: {
                  [Sequelize.Op.not]: null
                }
              },
              {
                actorId
              }
            ]
          },
          include: [
            {
              attributes: [ 'id', 'url' ],
              model: ActorModel.unscoped()
            }
          ]
        },
        {
          model: VideoChannelModel.unscoped(),
          required: true,
          include: [
            {
              attributes: [ 'name' ],
              model: AccountModel.unscoped(),
              required: true,
              include: [
                {
                  attributes: [ 'id', 'url', 'followersUrl' ],
                  model: ActorModel.unscoped(),
                  required: true
                }
              ]
            },
            {
              attributes: [ 'id', 'url', 'followersUrl' ],
              model: ActorModel.unscoped(),
              required: true
            }
          ]
        },
        VideoFileModel,
        TagModel
      ]
    }

    return Bluebird.all([
      // FIXME: typing issue
      VideoModel.findAll(query as any),
      VideoModel.sequelize.query(rawCountQuery, { type: Sequelize.QueryTypes.SELECT })
    ]).then(([ rows, totals ]) => {
      // totals: totalVideos + totalVideoShares
      let totalVideos = 0
      let totalVideoShares = 0
      if (totals[0]) totalVideos = parseInt(totals[0].total, 10)
      if (totals[1]) totalVideoShares = parseInt(totals[1].total, 10)

      const total = totalVideos + totalVideoShares
      return {
        data: rows,
        total: total
      }
    })
//.........这里部分代码省略.........

  [ScopeNames.AVAILABLE_FOR_LIST]: (options: {
    actorId: number,
    hideNSFW: boolean,
    filter?: VideoFilter,
    withFiles?: boolean,
    accountId?: number,
    videoChannelId?: number
  }) => {
    const accountInclude = {
      attributes: [ 'id', 'name' ],
      model: AccountModel.unscoped(),
      required: true,
      where: {},
      include: [
        {
          attributes: [ 'id', 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
          model: ActorModel.unscoped(),
          required: true,
          where: VideoModel.buildActorWhereWithFilter(options.filter),
          include: [
            {
              attributes: [ 'host' ],
              model: ServerModel.unscoped(),
              required: false
            },
            {
              model: AvatarModel.unscoped(),
              required: false
            }
          ]
        }
      ]
    }

    const videoChannelInclude = {
      attributes: [ 'name', 'description', 'id' ],
      model: VideoChannelModel.unscoped(),
      required: true,
      where: {},
      include: [
        {
          attributes: [ 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
          model: ActorModel.unscoped(),
          required: true,
          include: [
            {
              attributes: [ 'host' ],
              model: ServerModel.unscoped(),
              required: false
            },
            {
              model: AvatarModel.unscoped(),
              required: false
            }
          ]
        },
        accountInclude
      ]
    }

    // Force actorId to be a number to avoid SQL injections
    const actorIdNumber = parseInt(options.actorId.toString(), 10)
    const query: IFindOptions<VideoModel> = {
      where: {
        id: {
          [Sequelize.Op.notIn]: Sequelize.literal(
            '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
          ),
          [ Sequelize.Op.in ]: Sequelize.literal(
            '(' +
            'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
            ' UNION ' +
            'SELECT "video"."id" AS "id" FROM "video" ' +
            'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
            'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
            'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
            'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
            'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber +
            ')'
          )
        },
        // Always list public videos
        privacy: VideoPrivacy.PUBLIC,
        // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
        [ Sequelize.Op.or ]: [
          {
            state: VideoState.PUBLISHED
          },
          {
            [ Sequelize.Op.and ]: {
              state: VideoState.TO_TRANSCODE,
              waitTranscoding: false
            }
          }
        ]
      },
      include: [ videoChannelInclude ]
    }
//.........这里部分代码省略.........

    sequelize.fn('max', sequelize.col('age')),

    // Will order by max(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // Will order by name on an associated User
    [User, 'name', 'DESC'],

    // Will order by name on an associated User aliased as Friend
    [{model: User, as: 'Friend'}, 'name', 'DESC'],

    // Will order by name on a nested associated Company of an associated User
    [User, Company, 'name', 'DESC']
  ]
})

Something.findOne({
  order: 'convert(user_name using gbk)'
})

Something.findOne({
  order: 'username DESC'
})

Something.findOne({
  order: sequelize.literal('convert(user_name using gbk)')
})