Question 89061 in PostgreSQL Question posted in
The official documentation can be found here.

Find conversation not working in sequelize findOne – Postgresql

I have been struggling for days trying to find if a conversation exists between two users using Sequelize. I have relationships between conversation, message, user and userConversations. Here is how a table can look like:

enter image description here

Here is my userConversations table, what I want is find if there is an existing conversation between 2 or x users.

Here are the relationship and the query :

db.conversations.hasMany(db.messages, {
  foreignKey: 'conversationId',
});
db.messages.belongsTo(db.conversations);

db.conversations.belongsToMany(db.users, { through: db.userConversations });
db.users.belongsToMany(db.conversations, { through: db.userConversations });

db.conversations.hasMany(db.userConversations, {
  foreignKey: 'conversationId',
});
db.userConversations.belongsTo(db.conversations);

db.messages.belongsTo(db.users, { as: 'sender' });

Here is the NOT working query, what I want is check if there is an existing converstion for given user ids like [2,3] or [1,3] or [x, y, ...]

  let conversationDB = await db.conversations.findOne({
    include: [
      {
        model: db.userConversations,
        where: { userId: { [Op.in]: [2, 3] } }, <=== HARDCODED [2,3] for the example
      },
  });

Here is the result I dont agree on :

{
   "id": 5,
   "userConversations": [
       {
           "id": 8,
           "conversationId": 5,
           "userId": 2
       }
   ]
}

I am expecting to match strictly [2, 3] so i am expecting to strictly have two entries, here is what i want

{
    "id": 5,
    "userConversations": [
        {
            "id": 9,
            "conversationId": 6,
            "userId": 2
        },
        {
            "id": 10,
            "conversationId": 6,
            "userId": 3
        }
    ]
}

Also tried this but not working

let conversationDB = await db.conversations.findOne({
    include: [
      {
        model: db.userConversations,
        where: { userId: { 2 } },
      },
      {
        model: db.userConversations,
        where: { userId: 3 },
      },
    ],
    group: ['conversations.id'],
    having: Sequelize.literal('COUNT(DISTINCT userConversations.userId) = 2'),
  });

Any hint or ideas would be so helpful, have a good day.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    For the first time i dont have any errors but now nothing is being returned except []

    let conversationDB = await db.userConversations.findAll({
    attributes: [
      'conversationId',
      [Sequelize.fn('COUNT', Sequelize.col('conversationId')), 'conversationCount'],
    ],
    include: [
      {
        model: db.conversations,
        attributes: [],
      },
    ],
    where: { userId: { [Op.in]: [1, 2] } },
    group: ['conversationId'],
    having: Sequelize.literal('count(conversation.id) = 2'),
  });

    Here is user userConversations table :

    enter image description here


  2. 0

    From SQL an easy aggregate method is possible for your data model.

    The logic is for the user a,b,c if we have a conversation whose count is 3 then the conversation exists between them.

    select count(conversationId), userId, conversationId where userId in (1,2,3) group by conversationId having count(conversationId) = 3

    So using this you can reverse find the conversation object.

    Like,

    models.userConversations.findAll({
    attributes: ['userId', 'conversationId', [Sequelize.fn('COUNT', Sequelize.col('conversationId')), 'conversationCount']],
    include: [
    {
        model: models.conversations,
        attributes: []
    }],
    where: { userId: { [Op.in]: [2, 3] } },
    group: ['conversationId'],
    having: { conversationCount: 2 }
});

    I am little rusty in Sequelize so don’t know if the syntax will work 100%. if it doesn’t and you find the correct syntax comment and I will edit the answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search