Id column in query when not selecting it, causing problems when using group by (MS SQL)

[Bondi French]

I am trying to replicate this query:

SELECT
      convert(date, ri.CreatedOn)
  , count(*) as Total
  , sum(convert(int, ri.isInLive)) as InLive
  FROM [Finviews].[Domain].[ReportInfo] as ri
  LEFT JOIN Finviews.Domain.ReportType rt ON ri.ReportTypeId = rt.Id
  WHERE rt.Code LIKE '10%'
  group by convert(date, ri.CreatedOn)
  order by convert(date, ri.CreatedOn) desc

and I used this code with Sequelize:

reportInfo.findAll({
  attributes: ['CreatedOn', [sequelize.fn('count', sequelize.col('IsDownloaded')),'Total'], [sequelize.fn('sum', sequelize.col('isInLive')), 'InLive' ]],
  //exclude: ['Id'],
  include: [{
    model: reportType,
    attributes: ['Code'],
    where: {
    Code: {
      $like: '10%'},
      }
    }],
  group: ['CreatedOn'],
  order: [
    ['CreatedOn', 'DESC']
  ]
 })

However it seems to generate this code:

SELECT [reportInfo].[Id],
       [reportInfo].[CreatedOn],
       count([IsDownloaded]) AS [Total],
       sum([isInLive]) AS [InLive],
       [ReportType].[Id] AS [ReportType.Id],
       [ReportType].[Code] AS [ReportType.Code]
FROM [Domain].[ReportInfo] AS [reportInfo]
INNER JOIN [Domain].[ReportType] AS [ReportType] ON [reportInfo].[ReportTypeId] = [ReportType].[Id]
AND [ReportType].[Code] LIKE N'10%'
GROUP BY [CreatedOn],
ORDER BY [reportInfo].[CreatedOn] DESC;

Any advice? the `exclude:[id]` didn't seem to have any effect.

Next question is: how do I use convert within attributes, so I can transform the `sequelize.col('isInLive')` to integers as it is necessary for the `sum` calculation?

[Bondi French]

I am using sequelize 3.19.3 with MS SQL.

[Bondi French]

OK, I found out about `sequelize.cast` so I can convert to integers however: as my error message was containing the following:

`[RequestError: Column 'Domain.ReportType.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.]`

I modified my query to this:

reportInfo.findAll({

attributes: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), [sequelize.fn('count', sequelize.col('IsDownloaded')),'Total'], [sequelize.fn('sum', sequelize.cast(sequelize.col('isInLive'), 'INTEGER')), 'InLive' ]],

exclude: ['Id'],

include: [{

model: reportType,

attributes: ['Code'],

where: {

Code: {

$like: '10%'},

},

group: ['ReportType.Id']

}],

group: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), 'ReportInfo.Id', 'ReportType.Code','ReportType.Id'],

order: [

[sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), 'DESC']

]

}) 

However this doesn't give a satisfactory output: and generated the following:

Executing (default): 

SELECT
   [reportInfo].[Id],

   CAST([CreatedOn] AS DATE),
   count([IsDownloaded]) AS [Total],
   sum(CAST([isInLive] AS INTEGER)) AS [InLive],
   [ReportType].[Id] AS [ReportType.Id],
   [ReportType].[Code] AS [ReportType.Code]
   FROM [Domain].[ReportInfo] AS [reportInfo]
   INNER JOIN [Domain].[ReportType] AS [ReportType] ON [reportInfo].[ReportTypeId] = [ReportType].[Id]
   AND [ReportType].[Code] LIKE N'10%'
   GROUP BY CAST([CreatedOn] AS DATE), [ReportInfo].[Id], [ReportType].[Id], [ReportType].[Code]
   ORDER BY CAST([CreatedOn] AS DATE) DESC;

Final output:

`Problem trying to retrieve data:  [TypeError: Cannot read property 'forEach' of undefined]`

Why is `Id` selected in the main and subquery? How can avoid to have them selected and also how can I avoid the selection of the attribute of the subquery (`ReportType.Code`) ?

On Friday, March 4, 2016 at 7:21:08 PM UTC+11, Bondi French wrote:

[Mick Hansen]

Sequelize adds the primary keys so it can do deduplication of cartesian products.
`raw: true` disables this so when you can you should use that for aggregates.

We need an `aggregate` option or something for finds that does not do a full raw but does slow deduplication because `id` can't be  added.

--

Mick Hansen

@mhansendev
mhansen.io

[Bondi French]

OK, putting `raw: true` helped but i still have a problem with my grouping.

The query looks like this now:

````

reportInfo.findAll({

attributes: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), [sequelize.fn('count', sequelize.col('IsDownloaded')), 'Total'],

[sequelize.fn('sum', sequelize.cast(sequelize.col('isInLive'), 'INTEGER')), 'InLive']

],

include: [{

model: reportType,

attributes: ['Code'],

where: {

Code: {

$like: '10%'

}

},

group: ['ReportType.Id']

}],

group: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), 'ReportInfo.Id', 'ReportType.Code', 'ReportType.Id'],

order: [

[sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), 'DESC']

],

limit: 10,

raw: true

})

````

However it gives me results by Date in full format (ex: Tue Mar 15 2016 11:00:00 GMT+1100 (AUS Eastern Daylight Time) when I want the date by day: (ie `2015-03-15`), should I use something different to CAST to achieve the grouping by Day?

[Mick Hansen]

We likely parse it as a javascript date, you can just convert it to a string on your end.

[Bondi French]

I am not sure I follow, it needs to be converted to a day date during the SQL query, otherwise the grouping per day doesn't work and we have a grouping by seconds/minutes currently.

[Mick Hansen]

It's converted to a DATE field, which we parse as a javascript date.

[Bondi French]

ok this lead to the right query, although your answers are a bit cryptic sometimes.

I put here the end query that achieved the desired result, in case it's useful for other Sequelize users:

 reportInfo.findAll({
       attributes: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), [sequelize.fn('count', sequelize.col('IsDownloaded')),'Total'], [sequelize.fn('sum',                   sequelize.cast(sequelize.col('isInLive'), 'INTEGER')), 'InLive' ]],
       include: [{
                 model: reportType,

                 attributes: [],

                 where: {
                         Code: {
                               $like: '10%'},
                         },

                 }],
       group: [sequelize.cast(sequelize.col('CreatedOn'), 'DATE')],

       order: [
              [sequelize.cast(sequelize.col('CreatedOn'), 'DATE'), 'DESC']
              ],

       raw: true
 })

[Mick Hansen]

I'm sorry about that, i try to keep up with all issues and atleast give some replies.
I'm really swamped with my work at the moment so i can't really take the time to dig into it too much, just don't want to leave people hanging and atleast give them some help :)

When we execute a select, we have information about what data type a column is. If we detect a Date'able type (DATE, TIMESTAMPTZ, etc) we convert that to a javascript date.
You would still be able to convert that javascript date back into required format.

[Bondi French]

I appreciate that you are lacking in resources for this kind of project. Thanks for your help.