Loopback4中包含的记录的限制 [英] Limit for included records in Loopback4

问题描述

我想查询运动模型对象并包括相关对象(关系:athletExerciseLogsExercise).但是我只需要1个相关的对象，这就是为什么我要在其中添加"limit:1"的原因.纳入范围:

I want to query Exercise model objects and include related objects (relation: athletExerciseLogsExercise). But I need only 1 related object, thats why I add "limit: 1" to inclusion scope:

exerciseController.find({
    include: [{
        relation: "athletExerciseLogsExercise",
        scope: {
            where: {
                userId: id
            },
            order: ['date DESC'],
            limit: 1
        }
    }, ]
});

型号

export class Exercise extends Entity {
  ...
  @hasMany(() => AthletExerciseLog, {keyTo: 'exerciseId'})
  athletExerciseLogsExercise?: AthletExerciseLog[];
  ...
}

SQL(来自调试)

SELECT
  "id",
  "userid",
  "exerciseid",
  "date"
  ...
FROM
  "public"."athletexerciselog"
WHERE
  "userid" = '65b9b7110230'
  AND "exerciseid" IN (
    'd67503d511bb',
    '46d156a58aee'
  )
ORDER BY  "date" DESC
LIMIT 1

问题:SQL结果仅包含1条记录，其中一条"exerciseid"='d67503d511bb'

Problem: SQL result contains only 1 record for one "exerciseid" = 'd67503d511bb'

问题:如何从"athletexerciselog"接收1条记录；对于每个练习"?

Question: how to receive 1 record from "athletexerciselog" for each "exerciseid" ?

推荐答案

我找到了以下解决方案，这可能是开销，但我找不到其他方法...

I found the following solution, it might be an overhead, but I couldn't find another way...

1-创建数据库视图:对于每个用户ID 和练习ID

1 - Create DB View: select only 1 (last) record for each userid and exerciseid

CREATE VIEW "v_athletexerciselog" AS  
  SELECT q.*
  FROM athletexerciselog q
  WHERE (
        q.id IN ( 
          SELECT max(i.id) AS max
           FROM athletexerciselog i
          WHERE (i.event IS NULL)
          GROUP BY i.userid, i.exerciseid
        )
  )

2-创建新的LB4模型和存储库使用自定义表格名称(视图名称)扩展原始模型

2 - Create a new LB4 Model and Repository extends original model with custom table name (view name)

@model({
  settings: {
    postgresql: {schema: 'public', table: 'v_athletexerciselog'}, // custom names
  },
})
export class VAthletExerciseLog extends AthletExerciseLog {
  constructor(data?: Partial<VAthletExerciseLog>) {
    super(data);
  }
}

3 - 将练习模型和存储库中的相关模型名称更改为 VAthletExerciseLog(新)

3 - change related model name in Exercise model and repository to VAthletExerciseLog (new)

export class Exercise extends Entity {
  ...
  @hasMany(() => VAthletExerciseLog, {keyTo: 'exerciseId'})
  athletExerciseLogsExercise?: VAthletExerciseLog[];
  ...
}

4-删除订单"和限制"来自查询:

4 - Remove "order" and "limit" from query:

exercise.find({
    include: [{
        relation: "athletExerciseLogsExercise",
        scope: {
            where: {
                userId: id
            },
            //order: ['date DESC'],
            //limit: 1
        }
    }, ]
});

这篇关于Loopback4中包含的记录的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！