1

Sequelize group by include models work correct in MySQL, but not in SQL Server, core codes are as following

Models

var topics = sequelize.define('topics', {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            allowNull: false,
            autoIncrement: true
        },
        title:{
            type:  DataTypes.STRING,
            allowNull: false
        }
});
var comment = sequelize.define('comment', {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            allowNull: false,
            autoIncrement: true
        },
        text: { type:DataTypes.TEXT, allowNull:false },
        topic_id: { type: DataTypes.INTEGER, allowNull:false }
});

// Association
comment.hasOne(topics, {foreignKey:"id", sourceKey:"topic_id"})
topics.hasMany(comment, { foreignKey:"topic_id", sourceKey:"id" })

// Query get error in SQL Server, but goes well in MySQL
topics.findAll({
        group:["topics.id"],
        include: [{ model:comment, attributes:[] }]
})

Above code runs well in mySQL, but when runs in SQL Server, I get error "Column 'topics.id' is invalid in the selected list...."

yuyicman
  • 95
  • 1
  • 8
  • https://stackoverflow.com/questions/18258704/sql-server-column-invalid-in-the-select-list-because-it-is-not-contained-in-e – Rohit Dalal Mar 26 '19 at 12:26
  • @RohitDalal, thanks for your reply, but I rarely know SQL, my problem is the query can't operate group action in SQL, like this: topics.findAll({ group:["topics.id"], include: [{ model:comment, attributes:[] }] }), if I remove group by , it goes well – yuyicman Mar 26 '19 at 13:13

1 Answers1

1

I've solved the problem, in MSSQL, the trick is you need to list all fields in the group, like following:

topics.findAll({
   group:["topics.id","topics.title"], // <---- need to list "topics.title"
   include: [{ model:comment, attributes:[] }],
   attributes:{
      include:[[sequelize.fn('count',sequelize.col('comments.id')),'participants']]
   }
})
yuyicman
  • 95
  • 1
  • 8