Limit for included records in Loopback4

Question

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
        }
    }, ]
});

Model

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

SQL (from debug)

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

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

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

Revengeful

Answer 1

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

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 - 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 - change related model name in Exercise model and repository to VAthletExerciseLog (new)

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

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

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

Revengeful