sequelize: Error 'values.map is not a function' when using Op.like inside Op.contains

What are you doing?

const Sequelize = require('sequelize');
const sequelize = new Sequelize('postgres://DB_CONNECTION_STRING');

// Test model
const Test1 = sequelize.define('test1',
    {
        id: {
            type: Sequelize.STRING,
            unique: true,
            allowNull: false,
            primaryKey: true,
        },
        colors: { 
            type: Sequelize.ARRAY(Sequelize.TEXT),
            set(new_colors) {
                const colors_array = new_colors
                    // split on comma
                    .split(',')
                    // trim whitespace
                    .map(kw1=>kw1.trim())
                    // filter out empty strings
                    .filter(kw2=>kw2)
                // set colors to be the new multi valued string
                this.setDataValue('colors', colors_array);
            },
        },
    }
);

sequelize
.sync({force:true})
.then(()=>{
    console.log('sync');
    const colors1 = 'red, green,blue,red';

    return Test1.create({
        id: 'foo1',
        title: 'foo1-title',
        colors: colors1,
    })
})
.then(()=>Test1.find({
    where: {
        colors: {
            [sequelize.Op.contains]: {
                [sequelize.Op.like]: 're%'
            }
        }
    }
}))
.then(item2=>{ 
    console.dir(item2.toJSON())
    process.exit();
})
.catch(error=>{
    console.error(error)
    process.exit();
})

What do you expect to happen?

A record ‘foo1’ gets created with colors=[‘red’,‘green’,‘blue’,‘red’]. A query for ‘re%’ should return this record.

What is actually happening?

Executing (default): DROP TABLE IF EXISTS "test1s" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "test1s" ("id" VARCHAR(255) NOT NULL UNIQUE , "colors" TEXT[], "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE ("id"), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'test1s' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
sync
Executing (default): INSERT INTO "test1s" ("id","colors","createdAt","updatedAt") VALUES ('foo1',ARRAY['red','green','blue','red']::TEXT[],'2017-10-30 15:17:34.127 +00:00','2017-10-30 15:17:34.127 +00:00') RETURNING *;
TypeError: values.map is not a function
    at ARRAY._stringify (/pgtest/node_modules/sequelize/lib/dialects/postgres/data-types.js:565:33)
    at ARRAY.stringify (/pgtest/node_modules/sequelize/lib/data-types.js:32:17)
    at Object.escape (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:913:32)
    at Object._whereParseSingleValueObject (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2383:41)
    at Object.whereItemQuery (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2081:21)
    at Utils.getComplexKeys.forEach.prop (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1944:25)
    at Array.forEach (<anonymous>)
    at Object.whereItemsQuery (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1942:35)
    at Object.getWhereConditions (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2410:19)
    at Object.selectQuery (/pgtest/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1130:28)
    at QueryInterface.select (/pgtest/node_modules/sequelize/lib/query-interface.js:1018:27)
    at Promise.try.then.then.then (/pgtest/node_modules/sequelize/lib/model.js:1563:34)
    at tryCatcher (/pgtest/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/pgtest/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/pgtest/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/pgtest/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/pgtest/node_modules/bluebird/js/release/promise.js:693:18)
    at Async._drainQueue (/pgtest/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (/pgtest/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (/pgtest/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:781:20)
    at tryOnImmediate (timers.js:743:5)

Dialect: postgres Dialect version: 7.1.2 Database version: 9.6.2 Sequelize version: 4.18.0 Tested with master branch: No

Note : Your issue may be ignored by maintainers if it’s not tested against master branch OR does not follow issue template.

About this issue

  • Original URL
  • State: open
  • Created 7 years ago
  • Reactions: 6
  • Comments: 15 (2 by maintainers)

Most upvoted comments

Removed pg, added pg@6, but still get the same error.

I see

    where: {
        colors: {
            [sequelize.Op.contains]: {
                [sequelize.Op.like]: 're%'
            }
        }
    }

the value of [sequelize.Op.contains] should be an array, maybe you can change it to

    where: {
        colors: {
            [sequelize.Op.contains]: [{
                [sequelize.Op.like]: 're%'
            }]
        }
    }

I think I’ve managed to achieve what you are looking for like that:

Sequelize.where(Sequelize.fn('ARRAY_TO_STRING', Sequelize.col('colors'), ','), 'ILIKE', 're%')

When I tried using an array I got this:

Error: Invalid value { [Symbol(like)]: 're%' }
    at Object.escape (/server/node_modules/sequelize/lib/sql-string.js:66:11)
    at Object.wrapper [as escape] (/server/node_modules/lodash/lodash.js:5213:19)
    at ARRAY.str.ARRAY[.values.map.value (/server/node_modules/sequelize/lib/dialects/postgres/data-types.js:588:22)
    at Array.map (<anonymous>)
    at ARRAY._stringify (/server/node_modules/sequelize/lib/dialects/postgres/data-types.js:580:33)
    at ARRAY.stringify (/server/node_modules/sequelize/lib/data-types.js:32:17)
    at Object.escape (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:923:32)
    at Object._whereParseSingleValueObject (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2429:41)
    at Object.whereItemQuery (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2131:21)
    at Utils.getComplexKeys.forEach.prop (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1994:25)
    at Array.forEach (<anonymous>)
    at Object.whereItemsQuery (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1992:35)
    at value.map.item (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2190:30)
    at Array.map (<anonymous>)
    at Object._whereGroupBind (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2189:21)
    at Object.whereItemQuery (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2102:19)
    at Utils.getComplexKeys.forEach.prop (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1994:25)
    at Array.forEach (<anonymous>)
    at Object.whereItemsQuery (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1992:35)
    at Object.getWhereConditions (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2456:19)
    at Object.selectQuery (/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1140:28)
    at QueryInterface.rawSelect (/server/node_modules/sequelize/lib/query-interface.js:1147:37)

Seems as though anything inside the array will be escaped as if a column value, so maybe this is not implemented yet?

I see

    where: {
        colors: {
            [sequelize.Op.contains]: {
                [sequelize.Op.like]: 're%'
            }
        }
    }

the value of [sequelize.Op.contains] should be an array, maybe you can change it to

    where: {
        colors: {
            [sequelize.Op.contains]: [{
                [sequelize.Op.like]: 're%'
            }]
        }
    }