unicloud临时表联表查询

新增于HBuilderX 3.2.6

在此之前JQL联表查询只能直接使用虚拟联表，而不能先对主表、副表过滤再生成虚拟联表。由于生成虚拟联表时需要整个主表和副表进行联表，在数据量大的情况下性能会很差。

使用临时表进行联表查询，可以先对主表或者副表进行过滤，然后在处理后的临时表的基础上生成虚拟联表。

仍以上面article、comment两个表为例

获取article_id为’1’的文章及其评论的数据库操作，在直接联表查询和使用临时表联表查询时写法分别如下

// 直接使用虚拟联表查询
const res = await db.collection('article,comment')
.where('article_id._value=="1"')
.get()

// 先过滤article表，再获取虚拟联表联表获取评论
const article = db.collection('article').where('article_id=="1"').getTemp() // 注意是getTemp不是get
const res = await db.collection(article, 'comment').get()

直接使用虚拟联表联表查询，在第一步生成虚拟联表时会以主表所有数据和副表进行联表查询，如果主表数据量很大，这一步会浪费相当多的时间。先过滤主表则没有这个问题，过滤之后仅有一条数据和副表进行联表查询。

临时表内可以使用如下方法

方法调用必须严格按照顺序，比如field不能放在where之前

where
field // 关于field的使用限制见下方说明
orderBy
skip
limit

  const article = db.collection('article').where('article_id=="1"').field('title').getTemp() // 此处过滤article表，仅保留title字段。会导致下一步查询时找不到关联关系而查询失败
  const res = await db.collection(article, 'comment').get()

组合出来的虚拟联表查询时可以使用的方法

方法调用必须严格按照顺序，比如foreignKey不能放在where之后

foreignKey // foreignKey自 HBuilderX 3.3.7版本支持
where
field // 关于field的使用限制见下方说明
orderBy
skip
limit

一般情况下不需要再对虚拟联表额外处理，因为数据在临时表内已经进行了过滤排序等操作。以下代码仅供演示，并无实际意义

const article = db.collection('article').getTemp()
const comment = db.collection('comment').getTemp()
const res = await db.collection(article, comment).orderBy('title desc').get() // 按照title倒序排列

field使用限制

• HBuilderX 3.3.7之前 field 内仅可以进行字段过滤，不可对字段重命名、进行运算，field('name as value')、field('add(score1, score2) as totalScore')都是不支持的用法
• HBuilderX 3.3.7及以上版本支持对字段重命名或运算
• 进行联表查询时仅能使用临时表内已经过滤的字段间的关联关系，例如上面article、comment的查询，如果换成以下写法就无法联表查询
• 不建议在虚拟联表内再对副表字段重命名或者运算，如果有此类需求应在临时表内进行，会出现预期之外的结果，为兼容旧版此用法仅输出警告不会抛出错误

权限校验

要求组成虚拟联表的各个临时表都要满足权限限制，即权限校验不会计算组合成虚拟联表之后使用的where、field

以下为一个订单表（order）和书籍表（book）的schema示例

// order schema
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": "doc.uid==auth.uid",
    "create": false,
    "update": false,
    "delete": false
  },
  "properties": {
    "id": { // 订单id
      "bsonType": "string"
    },
    "book_id": { // 书籍id
      "bsonType": "string"
    },
    "uid": { // 用户id
      "bsonType": "string"
    }
  }
}

// book schema
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true,
    "create": false,
    "update": false,
    "delete": false
  },
  "properties": {
    "id": { // 书籍id
      "bsonType": "string"
    },
    "name": { // 书籍名称
      "bsonType": "string"
    }
  }
}

如果先对主表进行过滤where('uid==$cloudEnv_uid')，则能满足权限限制（order表的"doc.uid==auth.uid"）

const order = db.collection('order')
.where('uid==$cloudEnv_uid') // 先过滤order表内满足条件的部分
.getTemp()

const res = await db.collection(order, 'book').get() // 可以通过权限校验

如果不对主表过滤，而是对虚拟联表（联表结果）进行过滤，则无法满足权限限制（order表的"doc.uid==auth.uid"）

const order = db.collection('order').getTemp()

const res = await db.collection(order, 'book').where('uid==$cloudEnv_uid').get() // 对虚拟联表过滤，无法通过权限校验

设置字段别名

联表查询时也可以在field内对字段进行重命名，写法和简单查询时别名写法类似，原字段名 as 新字段名即可。简单查询时的字段别名

仍以上述order、book两个表为例，以下查询将联表查询时order表的quantity字段重命名为order_quantity，将book表的title重命名为book_title、author重命名为book_author

// 客户端联表查询
const db = uniCloud.database()

const order = db.collection('order').field('book_id,quantity').getTemp()
const book = db.collection('book').field('_id,title as book_title,author as book_author').getTemp()
db.collection(order, book)
  .where('book_id.book_title == "三国演义"') // 如果field内对副表字段title进行了重命名，where方法内则需要使用重命名之后的字段名
  .get()
  .then(res => {
    console.log(res);
  }).catch(err => {
    console.error(err)
  })

查询结果如下

{
    "code": "",
    "message": "",
    "data": [{
        "_id": "b8df3bd65f8f0d06018fdc250a5688bb",
        "book_id": [{
            "book_author": "罗贯中",
            "book_title": "三国演义"
        }],
        "order_quantity": 555
    }, {
        "_id": "b8df3bd65f8f0d06018fdc2315af05ec",
        "book_id": [{
            "book_author": "罗贯中",
            "book_title": "三国演义"
        }],
        "order_quantity": 333
    }]
}

手动指定使用的foreignKey

如果存在多个foreignKey且只希望部分生效，可以使用foreignKey来指定要使用的foreignKey

2021年4月28日10点前此方法仅用于兼容JQL联表查询策略调整前后的写法，在此日期后更新的clientDB（上传schema、uni-id均会触发更新）才会有指定foreignKey的功能，关于此次调整请参考：联表查询策略调整

例：

数据库内schema及数据如下：

// comment - 评论表

// schema
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true,
    "create": false,
    "update": false,
    "delete": false
  },
  "properties": {
    "comment_id": {
      "bsonType": "string"
    },
    "content": {
      "bsonType": "string"
    },
    "article": {
      "bsonType": "string",
      "foreignKey": "article.article_id"
    },
    "sender": {
      "bsonType": "string",
      "foreignKey": "user.uid"
    },
    "receiver": {
      "bsonType": "string",
      "foreignKey": "user.uid"
    }
  }
}

// data
{
  "comment_id": "1-1",
  "content": "comment1-1",
  "article": "1",
  "sender": "1",
  "receiver": "2"
}
{
  "comment_id": "1-2",
  "content": "comment1-2",
  "article": "1",
  "sender": "2",
  "receiver": "1"
}
{
  "comment_id": "2-1",
  "content": "comment2-1",
  "article": "2",
  "sender": "1",
  "receiver": "2"
}
{
  "comment_id": "2-2",
  "content": "comment2-2",
  "article": "2",
  "sender": "2",
  "receiver": "1"
}

// article - 文章表

// schema
{
  "bsonType": "object",
  "required": [],
  "permission": {
    "read": true,
    "create": false,
    "update": false,
    "delete": false
  },
  "properties": {
    "article_id": {
      "bsonType": "string"
    },
    "title": {
      "bsonType": "string"
    },
    "content": {
      "bsonType": "string"
    },
    "author": {
      "bsonType": "string",
      "foreignKey": "user.uid"
    }
  }
}

// data
{
  "article_id": "1",
  "title": "title1",
  "content": "content1",
  "author": "1"
}
{
  "article_id": "2",
  "title": "title2",
  "content": "content2",
  "author": "1"
}
{
  "article_id": "3",
  "title": "title3",
  "content": "content3",
  "author": "2"
}

const comment = db.collection('comment').where('comment_id == "1-1"').getTemp()
const user = db.collection('user').field('uid,name').getTemp()
db.collection(comment, user)
.foreignKey('comment.receiver') // 仅使用comment表内receiver字段下的foreignKey进行主表和副表之间的关联
.get()