CODEWITHSUNDEEP
mysqlnode.jssequelize.js
i.brod
i.brod

Reputation: 4603

Sequelize with MYSQL: Raw query returns a "duplicate" result

I have this method that performs a raw query:

 Friendship.getFriends= async (userId)=>{

      const result = await sequelize.query(`select id,email from users where 
      users.id in(SELECT friendId FROM friendships where friendships.userId = 
     ${userId})`);

      return result;
   };

The result seems to contain the same exact data, but twice:

[ [ TextRow { id: 6, email: '[email protected]' },
TextRow { id: 1, email: '[email protected]' } ],
[ TextRow { id: 6, email: '[email protected]' },
TextRow { id: 1, email: '[email protected]' } ] ]

Only two records should actually be found by this query(id's 1 and 6), yet it returns an array with the same records twice.

Can somebody explain me what's going on here?

Edit: the models:

module.exports = (sequelize, DataTypes) => {
   const User = sequelize.define('User', {
     email: { type: DataTypes.STRING, unique: true },    
     password: DataTypes.STRING,
     isActive:{type:DataTypes.BOOLEAN,defaultValue:true}
  });

module.exports = (sequelize, DataTypes) => {
   const Friendship = sequelize.define('Friendship', {
     userId: DataTypes.INTEGER,    
     friendId: DataTypes.INTEGER,      
  });

Upvotes: 8

Views: 6545

Answers (3)

Arturo Mendes
Arturo Mendes

Reputation: 738

TL;DR;

Use a query type to avoid duplicates being returned when the database is either MySQL or MSSQL

sequelize.query(yourQuery, {type: QueryTypes.SELECT})

Explanation

I'm answering this question because the two answers available up to this day do not explain why this happens.

As per the documentation of Sequelize:

By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc). Note that since this is a raw query, the metadata are dialect specific. Some dialects return the metadata "within" the results object (as properties on an array). However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.

To avoid this behaviour you may tell Sequelize how to format the results using a query type as shown above.

Upvotes: 1

Prayag C. Patel
Prayag C. Patel

Reputation: 143

I am not sure but try below code.

const result = await sequelize.query("select id,email from users where 
      users.id in(SELECT friendId FROM friendships where friendships.userId = 
     ${userId})", {type: sequelize.QueryTypes.SELECT});

One more thing : Use join instead of in

Upvotes: 3

user123
user123

Reputation: 311

Try to set query type to SELECT in the second argument.

sequelize.query(queryString, {type: sequelize.QueryTypes.SELECT})

Upvotes: 22

Related Questions