CODEWITHSUNDEEP
sqljoinincludesequelize.jslimit
Huu Tin
Huu Tin

Reputation: 67

Sequelize Include, join, limit associated tables

I have a problem with the sequelize query, I think they should give the same rerult, but they don't. Can someone please explain this situation?
The first case:

getLatestStation = (stationId) => {
    return models.MonitoringDataInfo.findAll({
      where: {stationId :stationId},
      attributes: ['id', 'stationId', 'sentAt'],
      order : [['sentAt', 'DESC']],
      limit: 1,
      include : [{model: models.MonitoringData, attributes: ['idData', 'indicator', 'value', 'unit', 'sensorStatus']}]
    })
  }


The first result:
enter image description here


The first query:
SELECT [MonitoringDataInfo].*, [MonitoringData].[id] AS [MonitoringData.id], [MonitoringData].[idData] AS [MonitoringData.idData], [MonitoringData].[indicator] AS [MonitoringData.indicator], [MonitoringData].[value] AS [MonitoringData.value], [MonitoringData].[unit] AS [MonitoringData.unit], [MonitoringData].[sensorStatus] AS [MonitoringData.sensorStatus] FROM (SELECT [MonitoringDataInfo].[id], [MonitoringDataInfo].[stationId], [MonitoringDataInfo].[sentAt] FROM [monitoring_data_info] AS [MonitoringDataInfo] WHERE [MonitoringDataInfo].[stationId] = N'mkkneh3uBrAkw9hepMlF' ORDER BY [MonitoringDataInfo].[sentAt] DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [MonitoringDataInfo] LEFT OUTER JOIN [monitoring_data] AS [MonitoringData] ON [MonitoringDataInfo].[id] = [MonitoringData].[idData] ORDER BY [MonitoringDataInfo].[sentAt] DESC;

The second case:

getLatestStation = (stationId) => {
    return models.Station.findAll({
      attributes: ['id', 'name', 'address', 'envIndex'],
      where : {
        id : stationId,
        publicStatus : 1
      },
      include : [{
        model: models.MonitoringDataInfo, 
        limit: 1,
        // where : {
        //   stationId: stationId
        // },
        attributes: ['id', 'stationId', 'sentAt'],
        order : [['sentAt', 'DESC']],
        limit : 1,
        include : [{model: models.MonitoringData, attributes: ['idData', 'indicator', 'value', 'unit', 'sensorStatus']}]
      }]
    })
  }


The second result:
enter image description here
The second query: SELECT [Station].[id], [Station].[name], [Station].[address], [Station].[envIndex] FROM [stations] AS [Station] WHERE [Station].[id] = N'mkkneh3uBrAkw9hepMlF' AND [Station].[publicStatus] = 1;
SELECT [MonitoringDataInfo].[id], [MonitoringDataInfo].[stationId], [MonitoringDataInfo].[sentAt], [MonitoringData].[id] AS [MonitoringData.id], [MonitoringData].[idData] AS [MonitoringData.idData], [MonitoringData].[indicator] AS [MonitoringData.indicator], [MonitoringData].[value] AS [MonitoringData.value], [MonitoringData].[unit] AS [MonitoringData.unit], [MonitoringData].[sensorStatus] AS [MonitoringData.sensorStatus] FROM [monitoring_data_info] AS [MonitoringDataInfo] LEFT OUTER JOIN [monitoring_data] AS [MonitoringData] ON [MonitoringDataInfo].[id] = [MonitoringData].[idData] WHERE [MonitoringDataInfo].[stationId] IN (N'mkkneh3uBrAkw9hepMlF') ORDER BY [MonitoringDataInfo].[sentAt] DESC, [MonitoringDataInfo].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

Upvotes: 0

Views: 718

Answers (1)

Anatoly
Anatoly

Reputation: 22803

The first query does not limit records from MonitoringData in contrast to second one. Try to indicate separate:true in include:

include : [{model: models.MonitoringData, separate: true, attributes: ['idData', 'indicator', 'value', 'unit', 'sensorStatus']}]

Upvotes: 1

Related Questions