LoopBackJS 3：如何查询conversation_members含指定ID的会话

问题说明 #

需要从conversation集合中筛选出conversation_members数组包含所有指定ID（如[123,456,789]）的会话，尝试两种查询写法均返回空数组，需修正where子句写法。

集合结构 #

"conversation" : [
  {
            "id": "e03e8b6b-725a-44aa-8181-56cdcebe9a45",
            "created_at": "2023-02-02T21:38:22.633Z",
            "conversation_name": "My Conversation",
            "is_group": true,
            "created_by": {
                "id": "123",
                "name": "John Doe"
            },
            "conversation_members": [
                {
                    "id": "123",
                    "name": "John Doe"
                },
                {
                    "id": "456",
                    "name": "Jane Doe"
                },
                {
                    "id": "789",
                    "name": "Jane Doe"
                }
            ]
        },
  {
            "id": "e03e8b6b-725a-44aa-8181-56cdcebe9a45",
            "created_at": "2023-02-02T21:38:22.633Z",
            "conversation_name": "My Conversation",
            "is_group": true,
            "created_by": {
                "id": "123",
                "name": "John Doe"
            },
            "conversation_members": [
                {
                    "id": "123",
                    "name": "John Doe"
                },
                {
                    "id": "456",
                    "name": "Jane Doe"
                },
                {
                    "id": "7891",
                    "name": "Jane Doe"
                }
            ]
        },
  {
            "id": "e03e8b6b-725a-44aa-8181-56cdcebe9a45",
            "created_at": "2023-02-02T21:38:22.633Z",
            "conversation_name": "My Conversation",
            "is_group": true,
            "created_by": {
                "id": "123",
                "name": "John Doe"
            },
            "conversation_members": [
                {
                    "id": "123",
                    "name": "John Doe"
                },
                {
                    "id": "456",
                    "name": "Jane Doe"
                },
                {
                    "id": "789",
                    "name": "Jane Doe1"
                }
            ]
        }
]

已定义的远程方法 #

Conversation.remoteMethod('getConversationsByMembers', {
        accepts: { arg: 'membersId', type: 'array', required: true },
        returns: { arg: 'conversations', type: 'array' },
        http: { path: '/getConversationsByMembers', verb: 'post' },
    });

尝试过的无效写法 #

写法一：使用all操作符 #

Conversation.getConversationsByMembers = function (membersId, cb) {
        Conversation.find(
            {
                where: {
                    conversation_members: {
                        all: [
                            {
                                id: {
                                    inq: membersId.map((id) => id.toString()),
                                },
                            },
                        ],
                    },
                },
            },
            function (err, conversations) {
                if (err) {
                    cb(err);
                } else {
                    cb(null, conversations);
                }
            },
        );
    };

写法二：使用elemMatch操作符 #

Conversation.getConversationsByMembers = function (membersId, cb) {
        Conversation.find(
            {
                where: {
                    conversation_members: {
                        elemMatch: {
                            id: {
                                inq: membersId.map((id) => id.toString()),
                            },
                        },
                    },
                },
            },
            function (err, conversations) {
                if (err) {
                    cb(err);
                } else {
                    cb(null, conversations);
                }
            },
        );
    };

正确的查询写法 #

要确保所有指定的成员ID都存在于conversation_members数组中，需要对每个ID创建一个conversation_members.id的匹配条件，并用and逻辑组合：

Conversation.getConversationsByMembers = function (membersId, cb) {
  // 将数字ID转为字符串，匹配集合中的字符串类型ID
  const memberIdsStr = membersId.map(id => id.toString());
  
  Conversation.find(
    {
      where: {
        // 对每个ID生成一个匹配条件，并用and组合，确保所有ID都存在
        and: memberIdsStr.map(id => ({
          'conversation_members.id': id
        }))
      },
    },
    function (err, conversations) {
      if (err) {
        cb(err);
      } else {
        cb(null, conversations);
      }
    },
  );
};

写法说明 #

• 'conversation_members.id'：LoopBack会自动匹配数组中是否存在元素的id等于指定值
• and逻辑：确保所有指定的ID都能在数组中找到对应的成员，符合“包含所有指定ID集合”的需求
• 转换ID为字符串：因为集合中的id是字符串类型，需与查询条件类型一致

内容的提问来源于stack exchange，提问作者Kerasiotis Ioannis