我的模型是：
module.exports = function(sequelize, DataTypes) {
var CommitFileStatistic;
return CommitFileStatistic = sequelize.define('CommitFileStatistic', {
additions: {
type: DataTypes.INTEGER,
allowNull: false
},
deletions: {
type: DataTypes.INTEGER,
allowNull: false
},
status: {
type: DataTypes.STRING,
allowNull: false
},
fileSize: {
type: DataTypes.INTEGER,
allowNull: true
},
levenshteinDelta: {
type: DataTypes.INTEGER,
allowNull: true
},
fileHash: {
type: DataTypes.STRING,
allowNull: true
}
}, {
classMethods: {
associate: function(models) {
CommitFileStatistic.belongsTo(models.Commit);
return CommitFileStatistic.belongsTo(models.SourceFile);
}
}
});
};
module.exports = function(sequelize, DataTypes) {
var SourceFile;
return SourceFile = sequelize.define('SourceFile', {
filename: {
type: DataTypes.STRING,
allowNull: false
}
}, {
classMethods: {
associate: function(models) {
return SourceFile.belongsTo(models.Repository);
}
}
});
};
module.exports = function(sequelize, DataTypes) {
var Commit;
return Commit = sequelize.define('Commit', {
sha: {
type: DataTypes.STRING,
allowNull: false
},
commitTime: {
type: DataTypes.INTEGER,
allowNull: false
},
message: {
type: DataTypes.TEXT,
allowNull: false
},
isParsed: {
type: DataTypes.BOOLEAN,
allowNull: false,
defaultValue: false
}
}, {
classMethods: {
associate: function(models) {
Commit.hasMany(models.Branch);
return Commit.hasMany(models.Commit, {
as: 'Parent',
through: 'ParentCommit'
});
}
}
});
};
我想执行一个基本上可以执行的查询：SELECT COUNT(*) AS fileCount, sf.* FROM CommitFileStatistics cfs, Commits c, SourceFiles sf WHERE cfs.CommitId = c.id AND cfs.SourceFileId = sf.id AND c.RepositoryId = 2 GROUP BY cfs.SourceFileId ORDER BY fileCount DESC但是我想使用ORM而不是原始查询。这可能吗？
您需要的是这样的东西：
return CommitFileStatistic.findAll({
attributes: [[Sequelize.fn('COUNT', '*'), 'fileCount']],
include: [
{ model: Commit, attributes: [] },
{ model: SourceFile, attributes: [] }
],
group: ['SourceFileId'],
order: [['fileCount', 'DESC']]
});
这将导致以下查询：
SELECT
`CommitFileStatistic`.`id`,
COUNT('*') AS `fileCount`,
`Commit`.`id` AS `Commit.id`,
`SourceFile`.`id` AS `SourceFile.id`
FROM
`commit_file_statistics` AS `CommitFileStatistic`
LEFT OUTER JOIN `commits` AS `Commit`
ON `Commit`.`id` = `CommitFileStatistic`.`CommitId`
LEFT OUTER JOIN `source_files` AS `SourceFile`
ON `SourceFile`.`id` = `CommitFileStatistic`.`SourceFileId`
GROUP BY `SourceFileId`
ORDER BY `fileCount` DESC;
这可能仅适用于sequelize 2.0版：）
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句