Seqelize: Retrieve latest of hasMany relationship-CodePudding

Here is my relationship:

KbDevice.hasMany(KbDeviceEvent, { foreignKey: 'kb_device_id', as: 'events' })

Here is my query:

  const allKbDevice = await KbDevice.findAll({
    attributes: ['id', 'make', 'model'],
    include: [
      {
        model: KbDeviceEvent,
        as: 'events',
        attributes: ['id', 'event', 'chamber_id','created_at'],
      },
    ],
  });

The issue is, instead of an array, I would like to get one KbDeviceEvent as the latest item based on created_at. I tried to use an include but it kept throwing an error. Can anyone help please?

Do you need to see the actual models to answer this question? If so, please comment.

Thank you,

EDIT:

Here is the result of the above query:

{
  "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
  "make": "Toyota",
  "model": "Lexus",
  "events": [
    {
      "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
      "event": "deposit",
      "chamber_id": 2,
      "created_at": "2022-09-01T22:36:19.849Z"
    },
  ]
}

And I'm looking for:

{
  "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
  "make": "Toyota",
  "model": "Lexus",
  "event": { // I am the latest event
    "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
    "event": "deposit",
    "chamber_id": 2,
    "created_at": "2022-09-01T22:36:19.849Z"
  }
}

CodePudding user response：

Query to fetch the associated model sorted by created_at:

const allKbDevice = await KbDevice.findAll({
    attributes: ['id', 'make', 'model'],
    include: [
      {
        model: KbDeviceEvent,
        as: 'events',
        attributes: ['id', 'event', 'chamber_id','created_at'],
      },
    ],
    order: [
      [{ model: KbDeviceEvent, as: 'events' }, 'created_at', 'DESC']
    ],
    limit: 1
});

Please note that this will always return the results in this manner:

{
  "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
  "make": "Toyota",
  "model": "Lexus",
  "events": [
    {
      "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
      "event": "deposit",
      "chamber_id": 2,
      "created_at": "2022-09-01T22:36:19.849Z"
    },
  ]
}

since KbDevice model has hasMany relation with KbDeviceEvent model. You can do allKbDevice['events'][0] in your code to get the single event. If you are open to modifying the query, then you can achieve an output which doesn't have any array. However, device would be embedded inside event object. The query would be:

const kbDeviceEvent = await KbDeviceEvent.findAll({
    attributes: ['id', 'event', 'chamber_id','created_at'],
    order: [
      ['created_at', 'DESC']
    ],
    limit: 1,
    include: [
      {
        model: KbDevice,
        as: 'device',
        attributes: ['id', 'make', 'model']
      },
    ]
});

This will output something like this:

{
  "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
  "event": "deposit",
  "chamber_id": 2,
  "created_at": "2022-09-01T22:36:19.849Z"
  "device": {
    "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
    "make": "Toyota",
    "model": "Lexus",
  }
}