0

I would know how can I write some query using FLOAT value with sequelize ?

I tried that :

const Stat = sequelize.define('stat', {
    ID         : { type: DataTypes.INTEGER, allowNull: false },
    displayName: { type: DataTypes.STRING, allowNull: false },
    shortName  : { type: DataTypes.STRING, allowNull: false },
    value      : { type: DataTypes.FLOAT(10, 1), allowNull: false }
},
{
    freezeTableName: true,
    timestamps: false
})
//-------------
const test = await Stat.findOne({
    where: {
        shortName: shortName,
        value    : 22.1
    },
    attributes: ['displayName', 'shortName', 'value']
})

but it writes :

SELECT `displayName`, `shortName`, `value` FROM `stat` AS `stat` WHERE `stat`.`shortName` = 'TC' AND `stat`.`value` = '22.1' LIMIT 1;

with SQL '22.1' =/= 22.1 then with this generated query, I can't fetch any rows.

Typically, I just want a way to have that instead :

SELECT `displayName`, `shortName`, `value` FROM `stat` AS `stat` WHERE `stat`.`shortName` = 'TC' AND `stat`.`value` = 22.1 LIMIT 1;

Thanks in advance to everyone who will help me !

EDIT I found this solutions that works well actually, but yeah- It's not that good... May I ask about opinions ?

const test = await Stat.findOne({
        attributes: ['displayName', 'shortName', 'value'],
        where: {
            shortName: shortName,
            value: sequelize.literal(`value = ${value}`)
        }
    })
Akihiro
  • 33
  • 5
  • Okay, with your question in mind, – do you get an error, or are you trying to prevent error from happening? If the later, I'd suggest not to overthink it and use the number literal, until you can't anymore – Parzh from Ukraine Nov 06 '22 at 20:38
  • I don't get any error. But this causes a problem, the query as it is generated does not fetch any rows, while the query as I want it, does – Akihiro Nov 06 '22 at 20:42
  • What DB you are using? – Emma Nov 08 '22 at 03:43
  • 1
    Im using mySQL with an innoDB engine – Akihiro Nov 08 '22 at 18:47
  • `value = '22.1'` is a strict equal so it only matches when the value is exactly 22.1. `value = 22.1` would allow a certain precision and I think that is what you are seeing. However, for comparing float, it is not always reliable to use equal. `Op.between` or `Op.gte` and `Op.lte` would be probably more robust. Please take a look at Number comparisons section in here https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#operators – Emma Nov 08 '22 at 19:35
  • ref: https://stackoverflow.com/a/8839548/2956135 – Emma Nov 08 '22 at 19:43

1 Answers1

0

Try adding a getter method to your value field, like so:

const Stat = sequelize.define(
  "stat",
  {
    ID: { type: DataTypes.INTEGER, allowNull: false },
    displayName: { type: DataTypes.STRING, allowNull: false },
    shortName: { type: DataTypes.STRING, allowNull: false },
    // Add a getter method here
    value: {
      type: DataTypes.FLOAT(10, 1),
      allowNull: false,
      get() {
        const data = this.getDataValue("value");
        return data === null ? null : parseFloat(data);
      },
    },
  },
  {
    freezeTableName: true,
    timestamps: false,
  }
);

Source: https://github.com/sequelize/sequelize/issues/8019

Jacob Cambell
  • 277
  • 1
  • 4