CODEWITHSUNDEEP
arraysnode.jspostgresqlmultidimensional-arraysequelize.js
dilipktr
dilipktr

Reputation: 81

SequelizeDatabaseError: operator does not exist: character varying[] @> character varying

I am getting the above error when i am trying to search one array value in array of arrays. My code: 

    sequelize.define('room', {
    'id' : {'type' : DataTypes.INTEGER, 'primaryKey' : true, 'autoIncrement' : true},
    'tutor' : {'type' : DataTypes.INTEGER, 'allowNull' : false},
    'students' : {'type' : DataTypes.ARRAY(DataTypes.STRING), 'allowNull' : false},
    'subject' : DataTypes.STRING,
    'date_time' : DataTypes.DATE,
    'status' : {'type' : DataTypes.STRING , 'defaultValue' : "active"},
  }
)

var queryString = "Select * from rooms where status = :statusVar and students @> ANY(:stuArray::character varying[]) ORDER BY CASE when :orderingVar = 'date_time DESC' then date_time end DESC, case when :orderingVar = 'date_time' THEN date_time end ASC OFFSET :offsetNumber LIMIT :limitNumber;"

    return sequelize.query(queryString,{ 
                      replacements:{ 
                        statusVar: status,
                        orderingVar: orderVar, 
                        limitNumber: itemPerPage, 
                        offsetNumber: ((pageNumber-1)*itemPerPage),
                        tutArray: '{' + tutorUserId.toString() +'}',
                        stuArray: ['{' + studName.toString() + '}']
                      }, 
                        type: sequelize.QueryTypes.SELECT})

where studName = [Emily, John, Dexter];

I want to find all records where 'students' column contains any of the student names i.e., Emily or John or Dexter

Upvotes: 3

Views: 2956

Answers (1)

dilipktr
dilipktr

Reputation: 81

I ended up doing the following and it worked

var queryString = "Select * from rooms where students && :stuArray::varchar[] and status = :statusVar ORDER BY CASE when :orderingVar = 'date_time DESC' then date_time end DESC, case when :orderingVar = 'date_time' THEN date_time end ASC OFFSET :offsetNumber LIMIT :limitNumber;"

'&&' is overlap (have elements in common) operator Ex: ARRAY[1,4,3] && ARRAY[2,1] returns true. Note that right side of the operator also should be an array in this format stuArray: ['{' + studName.toString() + '}'] i.e., ['{Emily, John, Dexter}']

Upvotes: 3

Related Questions