mysqlnode.jsormsails.jswaterline

Waterline - Where with sum of fields


I've got the following model Test

module.exports = {
    attributes: {
        a: {
            type: 'number'
        },
        b: {
            type: 'number'
        }
    }
}

I would like to build a query that allows me to put sum of fields a and b in where statement.

SQL equavilent:

SELECT * FROM Test WHERE a + b = myValue

I read in sails doc's about Criteria modifiers but there is no word about that.

Is there any clever way to do that? Of course I can use native query but I would like to avoid that because I must use the sum along with other modifiers. The reason is I'm generating dynamic queries from separate files and with native queries I will have to also handle already defined functionality like or, and, etc.


Solution

  • I found a workaround. Maybe it will be useful to someone. It is not stricte sails/node solution, but database one, however, it fits my case perfectly.

    From MySQL 5.7 there is something like generated columns.

    Columns are generated because the data in these columns are computed based on predefined expressions.

    All I had to do was add an extra, auto generated column to my Test model:

    module.exports = {
        attributes: {
            a: {
                type: 'number',
                columnType: 'int'
            },
            b: {
                type: 'number',
                columnType: 'int'
            },
            c: {
                type: 'number',
                columnType: 'int GENERATED ALWAYS AS(a + b) VIRTUAL'
            }
        }
    }
    

    Now I'm able to do such query:

    const result = await Test.find({ c: 2 })
    

    ...and I get the correct result. Waterline treats my column like any other, database does everything instead of me.

    Of course I can mix it with other modifiers with no problems.

    I haven't seen any complications so far.