node.jspostgresqlnestjssequelize.jssequelize-typescript

How to filter associated models connected using through table and keeping all associated model


I want to filter conversations based on a user Id and get all users in that conversation. Users and conversations are joined using a through model

This is my approach

user.entity.ts

@BelongsToMany(Conversation,() => ConversationMember)
conversations : Conversation[]

conversation.entity.ts

@BelongsToMany(User , () => ConversationMember)
members: User[]

service.ts

const allConversations = await Conversations.findAndCountAll({
       where: {
              $members.id$: userId
              },
       attributes: [ 'id', 'createdAt'],
       include:[
      {
        model: User,
        as: 'members',
        attributes: ['id', 'username', 'firstName', 'lastName'],
        through: {
          attributes: [],
        },
      ]
});

This returns an error"

"missing FROM-clause entry for table "members"",

I want to filter conversations using user Id and get all members of the conversation


Solution

  • const allConversations = await Conversations.findAndCountAll({
               where: {
                       id: {
                [Op.in]: Sequelize.literal(`(
                  SELECT "conversationId"
                  FROM "conversation-members"
                  WHERE "userId" = '${userId}'
                )`),
              }
                      },
               attributes: [ 'id', 'createdAt'],
               include:[
              {
                model: User,
                as: 'members',
                attributes: ['id', 'username', 'firstName', 'lastName'],
                through: {
                  attributes: [],
                },
              ]
        });
    

    This worked for me , hopefully it helps anyone with a similar problem