How do I select a column using an alias
Also Sequelize supports defining column names directly on the model definition too.
Sequelize Docs On that they mention about field
attribute on column definition.
ex: (Taken from Docs itself)
const { Model, DataTypes, Deferrable } = require("sequelize");
class Foo extends Model { }
Foo.init({
// You can specify a custom column name via the 'field' attribute:
fieldWithUnderscores: {
type: DataTypes.STRING,
field: 'field_with_underscores'
},
}, {
sequelize,
modelName: 'foo'
});
thanks to this answer
Table.findAll({
attributes: ['id', ['first', 'firstName']] //id, first AS firstName
})
.then(function(posts) {
res.json(posts);
});
You need to use row.get('newname')
to access columns aliased by attributes
Doing just row.newname
, or row.oldname
, will not work like it does for non-aliased names for some reason:
https://github.com/sequelize/sequelize/issues/10592
https://sequelize.org/v5/manual/models-usage.html documents it:
Project.findOne({ where: {title: 'aProject'}, attributes: ['id', ['name', 'title']] }).then(project => { // project will be the first entry of the Projects table with the title // 'aProject' || null // project.get('title') will contain the name of the project })
but https://sequelize.org/master/class/lib/model.js~Model.html doesn't mention it, which is confusing:
instance.field // is the same as instance.get('field')
Related: Sequelize cannot access alias. Alias is undefined
Minimal runnable example:
const assert = require('assert');
const path = require('path');
const { Sequelize, DataTypes, Op } = require('sequelize');
const sequelize = new Sequelize({
dialect: 'sqlite',
storage: path.basename(__filename) + '.sqlite',
});
(async () => {
const Int = sequelize.define('Int', {
value: {
type: DataTypes.INTEGER,
},
name: {
type: DataTypes.STRING,
},
}, {});
await Int.sync({force: true})
await Int.create({value: 2, name: 'two'});
let row;
row = await Int.findOne({
where: { value: 2 },
attributes: [ 'id', [ 'value', 'newvalue' ] ],
});
assert.strictEqual(row.id, 1);
assert.strictEqual(row.value, undefined);
assert.strictEqual(row.newvalue, undefined);
assert.strictEqual(row.get('newvalue'), 2);
await sequelize.close();
})();
The generated query does exactly what we wanted then:
SELECT `id`, `value` AS `newvalue` FROM `Ints` AS `Int`
WHERE `Int`.`value` = 2 LIMIT 1;
tested on sequelize 6.5.1, sqlite3 5.0.2.