Appwrite虚拟关联属性查询报错及数据结构优化求助

问题概述 #

开发React Native应用时，使用Appwrite实现帖子点赞功能，更新Videos集合的likebyusers关联字段已成功，但调用查询用户点赞帖子的接口时，触发错误：

AppwriteException: Invalid query: Cannot query on virtual relation attribute

相关代码与返回结果 #

点赞功能代码 #

export async function likeVideoPost(postId, userId) {
  try {
    const posts = await databases.listDocuments(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      [Query.equal("$id", postId)]
    );
    if (!posts) throw new Error("Something went wrong [post]");
    const postDocument = posts.documents[0];
    const existingLikeByUsers = postDocument.likebyusers;
    const result = await getCurrentUser();
    const updatedLikeByUsers = [...existingLikeByUsers, result];
    
    const updatedPost = await databases.updateDocument(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      [postId],
      {
        likebyusers: updatedLikeByUsers,
      }
    );
    return updatedPost;
  } catch (error) {
    throw new Error(error);
  }
}

接口返回结果 #

{
  "$collectionId": "xxx",
  "$createdAt": "2024-04-22T16:03:18.362+00:00",
  "$databaseId": "xxx",
  "$id": "xxx",
  "$permissions": [
    "read(\"user:xxx\")",
    "update(\"user:xxx\")",
    "delete(\"user:xxx\")"
  ],
  "$updatedAt": "2024-04-22T17:40:05.047+00:00",
  "creator": {
    "$collectionId": "xxx",
    "$createdAt": "2024-04-22T16:02:44.297+00:00",
    "$databaseId": "xxx",
    "$id": "66268a2447ea7ce4f6b3",
    "$permissions": [],
    "$updatedAt": "2024-04-22T16:02:44.297+00:00",
    "accountId": "xxx",
    "avatar": "https://cloud.appwrite.io/v1/avatars/initials?name=Test&project=xxx",
    "email": "xxx",
    "password": "xxx",
    "username": "Test",
    "videos": [[Object], [Object]]
  },
  "likebyusers": [
    {
      "$collectionId": "xxx",
      "$createdAt": "2024-04-22T16:02:44.297+00:00",
      "$databaseId": "xxx",
      "$id": "xxx",
      "$permissions": [Array],
      "$updatedAt": "2024-04-22T16:02:44.297+00:00",
      "accountId": "xxx",
      "avatar": "https://cloud.appwrite.io/v1/avatars/initials?name=Test&project=xxx",
      "email": "xxx",
      "password": "xxx",
      "username": "Test"
    },
    {
      "$collectionId": "xxx",
      "$createdAt": "2024-04-22T16:42:10.526+00:00",
      "$databaseId": "xxx",
      "$id": "xxx",
      "$permissions": [Array],
      "$updatedAt": "2024-04-22T16:45:42.462+00:00",
      "accountId": "xxx",
      "avatar": "https://cloud.appwrite.io/v1/avatars/initials?name=Test2&project=xxx",
      "email": "xxx",
      "password": "xxx",
      "username": "Test2"
    }
  ],
  "prompt": "Abcprompt",
  "thumbnail": "https://cloud.appwrite.io/v1/storage/buckets/6623dbe60f9b23bfe619/files/66268a4511a67cbf7772/preview?width=2000&height=2000&gravity=top&quality=100&project=xxx",
  "title": "Abc",
  "video": "https://cloud.appwrite.io/v1/storage/buckets/6623dbe60f9b23bfe619/files/66268a45619cf6629001/view?project=xxx"
}

查询用户点赞帖子代码 #

// Get video posts Liked by user
export async function getUserLikedPosts(userId) {
  try {
    const posts = await databases.listDocuments(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      [Query.equal("likebyusers", [userId])]
    );
    return posts.documents;
  } catch (error) {
    console.log(error);
    throw new Error(error);
  }
}

数据集合结构 #

Users集合 #

Videos集合 #

使用版本 #

• react-native-appwrite: ^0.2.2
• @babel/core: ^7.24.3
• tailwindcss: 3.3.2
• expo: ~50.0.14

错误原因与解决方法 #

错误原因 #

1. 虚拟关系字段无法查询：Appwrite中，若likebyusers是反向关联的虚拟关系（即关系数据存储在Users集合而非Videos集合），则无法直接在Videos集合中对该字段执行查询操作。
2. 点赞代码的错误写法：更新likebyusers时传入了完整的用户对象而非用户文档ID，导致字段存储格式异常；同时updateDocument的第三个参数错误传入了数组[postId]，应为单个文档ID字符串。
3. 查询参数错误：查询时使用[userId]作为匹配值，而Query.equal针对数组类型字段只需传入单个值，Appwrite会自动检查数组是否包含该值。

修复步骤 #

1. 修正点赞功能代码

更新时仅传递用户文档ID，而非完整对象，并修正updateDocument的参数错误：

export async function likeVideoPost(postId) {
  try {
    const currentUser = await getCurrentUser();
    if (!currentUser) throw new Error("用户未登录");

    const posts = await databases.listDocuments(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      [Query.equal("$id", postId)]
    );
    if (!posts.documents.length) throw new Error("帖子不存在");
    
    const postDocument = posts.documents[0];
    // 提取已点赞用户的ID，避免重复点赞
    const existingLikeIds = postDocument.likebyusers.map(user => user.$id);
    if (existingLikeIds.includes(currentUser.$id)) {
      return postDocument; // 已点赞，无需重复操作
    }

    const updatedLikeIds = [...existingLikeIds, currentUser.$id];
    const updatedPost = await databases.updateDocument(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      postId, // 修正为单个文档ID
      { likebyusers: updatedLikeIds }
    );
    return updatedPost;
  } catch (error) {
    throw new Error(error);
  }
}

2. 修正查询代码

将查询参数改为单个用户ID：

export async function getUserLikedPosts(userId) {
  try {
    const posts = await databases.listDocuments(
      appwriteConfig.databaseId,
      appwriteConfig.videoCollectionId,
      [Query.equal("likebyusers", userId)]
    );
    return posts.documents;
  } catch (error) {
    console.log(error);
    throw new Error(error);
  }
}

3. 调整关系存储配置

在Appwrite控制台的Videos集合中，确保likebyusers关系字段的存储位置设置为当前集合（即Videos集合），而非反向关联到Users集合。这样该字段会存储用户ID数组，属于可查询的实际字段，而非虚拟属性。

集合结构优化建议 #

1. 改用中间集合实现多对多点赞关系 #

创建Likes中间集合，记录用户与帖子的点赞关联，结构如下：

优势：

• 避免Videos集合中存储大量用户ID数组，提升查询性能
• 可扩展记录点赞时间、取消点赞等操作日志
• 查询用户点赞帖子时，可通过Likes集合关联查询，更灵活

2. 移除敏感字段 #

Users集合中无需存储password字段，Appwrite的Account服务已负责用户密码的安全存储，重复存储会带来安全风险，直接删除该字段。

3. 优化权限配置 #

当前Videos文档的权限仅允许创建者读写，若需公开可见，可将权限改为：

• read("any")：允许所有用户读取
• update("user:$creator")：仅允许创建者更新
• delete("user:$creator")：仅允许创建者删除

4. 规范字段命名 #

将likebyusers改为likedByUsers，符合驼峰命名规范，提升代码可读性。

5. 控制返回字段 #

查询时使用Query.select()参数只返回需要的字段，避免返回email等敏感信息：

// 示例：查询帖子时仅返回必要字段
const posts = await databases.listDocuments(
  appwriteConfig.databaseId,
  appwriteConfig.videoCollectionId,
  [
    Query.equal("likebyusers", userId),
    Query.select(["title", "thumbnail", "creator.username"])
  ]
);

内容的提问来源于stack exchange，提问作者meet Patel