Problem constructing query with sequelize

Question

I am having problems recreating the following query in Sequelize. I have been sitting for hours trying to figure this out.

I am using camelCase instead of snake_case so I cannot reuse the same query.

The query does not work if I exchange all snake_cased variables to camelCase in the query. I get back "relation \"directmessages\" does not exist". I cannot access the directmessages table in psql with TABLE directmessages; - but I know its supposed to be there.

I can also not figure out how to do the same query with sequelize.

Basically I need to fetch every User the current user has already direct messaged with. Either as a sender or receiver.

  models.sequelize.query(
    'select distinct on (u.id) u.id, u.username from users as u join direct_messages as dm on (u.id = dm.sender_id) or (u.id = dm.receiver_id) where (:currentUserId = dm.sender_id or :currentUserId = dm.receiver_id) and dm.team_id = :teamId',
    {
      replacements: { currentUserId: user.id, teamId: id },
      model: models.User,
      raw: true,
    },

These are the relevant models for this query:

User Model:

User.associate = (models) => {
    User.belongsToMany(models.Team, {
        through: models.Member,
        foreignKey: 'userId',
    })
    User.belongsToMany(models.Channel, {
        through: 'channel_member',
        foreignKey: 'userId',
    })

}

DirectMessage Model:

DirectMessage.associate = (models) => {
    DirectMessage.belongsTo(models.Team, {
        foreignKey: 'teamId',
    })
    DirectMessage.belongsTo(models.User, {
        foreignKey: 'receiverId',
    })
    DirectMessage.belongsTo(models.User, {
        foreignKey: 'senderId',
    })
}

I tried creating the query with sequelize like this:

models.User.findAll({ 
            include:[{
                model: models.DirectMessage,
                where: {                        
                    teamId, 
                    [models.sequelize.Op.or]: [{senderId: user.id}, {receiverId: user.id}]
                }
            }]
            }, { raw: true })

I get back "message": "directMessage is not associated to user!", which I presume is because DirectMessage is associated to User, but not the other way around.

Does anybody have any tips of how I can reconstruct this query?

Tried dropping the database, recreating all data. Get the following when trying to fetch directMessageMembers error: "message": "relation \"directmessages\" does not exist" — Alexander
– Alexander, Commented Mar 6, 2019 at 14:37

KenOn10 · Accepted Answer · 2019-03-06 22:33:13Z

which I presume is because DirectMessage is associated to User, but not the other way around.

I would presume so, too. It's easy enough to associate from User to DirectMessage (twice):

User.hasMany(models.DirectMessage, {foreignKey: 'receiverId', as: 'receivers'});        
User.hasMany(models.DirectMessage, {foreignKey: 'senderId', as: 'senders'});

Then the query isn't too bad. Note that the "as" value tells Sequelizer which FK to use, so make sure the hasMany matches the queries' include model

    models.Users.findAll({
        include: [
            {
                model: models.DirectMessage, as: 'receivers',
                attributes:  [[Sequelize.fn('count', 'id'), 'number_received']]
            },
            {
                model: models.DirectMessage, as: 'senders',
                attributes: [[Sequelize.fn('count', 'id'), 'number_sent']]
            }
            ],
        where : {id : user.id}, // parameter
        group : ['id']
    })

You'd then have to verify that either "senders.number_sent" or "receivers.number_received" are not null and >0. Theoretically, you could do this with a HAVING clause, but IMHO this isn't implemented too well in Sequelize....

Ellebkey · Accepted Answer · 2019-03-06 22:33:26Z

There are a couple of things here. First, when you use belongsToMany you need to do te association on both models, because this is a M:N relation, like this: Also you have the wrong foreignKey on the association. Should be something like this:

User.associate = (models) => {
  User.belongsToMany(models.Team, {
      through: models.Member,
      foreignKey: 'user_id',
  })

  models.Team.belongsToMany(User, {
      through: models.Member,
      foreignKey: 'team_id',
  })

  // ----------------------------------
  User.belongsToMany(models.Channel, {
      through: 'channel_member',
      foreignKey: 'user_id',
  })

  models.Channel.belongsToMany(user, {
      through: 'channel_member',
      foreignKey: 'channel_id',
  })

}

Now to be able to use camelCase names on javascript and sequelize and keep snake_case on sql you can do this:

module.exports = (sequelize, DataTypes) => {
  const Member = sequelize.define('Member', {
    // here goes your regular fields
    userId: { //now you can use this property on sequelize
      field: 'user_id', //and is linked yo the foreignKey
      type: DataTypes.INTEGER
    },
    teamId: {
      field: 'team_id',
      type: DataTypes.INTEGER
    }
  }
}

And the last issue that you have with the include, is because you have to specify which association are you calling, and as you said, you need to declare the association the other way, from User to DirectMessage:

User.hasMany(models.DirectMessage, { //there is also hasOne()
  as: 'Sender', // this is important because you have two association from User to  DirectMessage
  foreignKey: 'sender_id',
})

User.hasMany(models.DirectMessage, { //there is also hasOne()
  as: 'Receiver', // this is important because you have two association from User to  DirectMessage
  foreignKey: 'user_id',
})

Now you can do your query

models.User.findAll({ 
  include:[{
      model: models.DirectMessage,
      as: 'Sender'
      where: {                        
          teamId,
      }
  }]
}, { raw: true })

I have my doubts at this query because in reality you are calling all the DirectMessages so the senderId and receiverId filter does not make much sense.

Anyway, hope with this have a better idea what to do.

Collectives™ on Stack Overflow

Problem constructing query with sequelize

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related