Skip to content

小程序关联表学习

Honye edited this page Sep 28, 2018 · 1 revision

关联表学习

文中代码并不是实际代码,伪代码不可直接运行。

功能:用户 喜欢 文章

  1. 用户表(users)

    id username
    唯一标识,没有登录的可直接使用 _openid 用户名
  2. 文章表(articles)

    id title content
    唯一标识 标题 内容
  3. 关联表(relation)

    index userId articleId
    唯一索引 用户 ID 文章 ID

用户喜欢文章时,向 relation 添加一条数据,

用户取消喜欢时,从 relation 删除对应的数据。

需求

  1. 查询文章列表,返回文章标题、喜欢人数、用户是否喜欢

    第一步:先查询出文章列表

    select * from articles

    第二步:遍历文章列表,查询关联表得到用户是否喜欢、喜欢人数

    for (article : articles) {
        isLike = ( select * from relation where articleId = article.id && userId = 'userId' )
        likeCount = ( select count(*) from relation where articleId = article.id )
        article.isLike = isLike
        article.likeCount = likeCount
    }
    const db = cloud.database()
    
    const getArticles = async (event, context) => {
        const { userInfo: { openId } } = event
        return db.collection('articles').get().then(({ data }) => {
            let articles = []
            for (let i = 0, length = data.length; i < length; ++i) {
                await Promise.all([
                    db.collection('relation').where({
                        articleId: data[i].id,
                    }).count(),
                    db.collection('relation').where({
                        articleId: data[i].id,
                        userId: openId,
                    }).count()
                ]).then(([likeCount, liked]) => {
                    articles.push({
                        ...data[i],
                        likeCount,
                        liked: !!liked,
                    })
                })
            }
            return {
                data: articles,
                message: 'success',
            }
        }).catch( err => {
            console.error(err.errMsg)
            return Promise.reject({
                data: [],
                message: err.errMsg,
            })
        })
    }
  2. 查询用户喜欢的文章列表,返回文章标题、喜欢人数

    第一步:查询关联表得到用户喜欢的文章 ID 数组

    select articleId from relation where userId = 'userId'

    第二步:遍历文章 ID 数组,查询文章表得到标题

    res = [] // 最终结果
    for (id : articleIds) {
        details = ( select * from articles where articleId = id )
        likeCount = ( select count(*) from relation where articleId = id )
        res.push({
            articleId: id,
            title: details.title,
            likeCount: likeCount,
        })
    }
    const db = cloud.database()
    const _ = db.command
    
    const getFavArticles = async (event, context) => {
        const { userInfo: { openId } } = event
        return db.collection('relation').where({
            userId: openId,
        }).field({
            articleId: true,
        }).get().then(({ data }) => {
            return db.collection('articles').where({
                id: _in(data.map( item => item.articleId )),
            }).then(({ data: articles }) => {
                let result = []
                for (let i = 0, length = articles.length; i < length; ++i) {
                	await db.collection('relation').where({
                        articleId: articles[i].id,
                    }).count().then(({ total }) => {
                        result.push({
                            ...articles,
                            likeCount: total,
                        })
                    })
            	}
                return {
                    data: result,
                    message: 'success',
                }
            })
        }).catch( err => {
            console.error(err)
            return Promise.reject({
                data: [],
                message: err.errMsg,
            })
        })
    }
  3. 查询文章详情,返回文章标题、内容、喜欢人数、用户是否喜欢

    select title, content, likeCount from articles
    select count(*) from relation where articleId = 'articleId' && userId = 'userId'
    const db = cloud.database()
    
    const getArticleDetails = (event, context) => {
        const { userInfo: { openId }, id } = event
        return Promise.all([
            // 如果直接使用微信自带的 _id 索引可直接使用
            // db.collection('articles').doc(id)
            db.collection('articles').where({ id }),
            db.collection('relation').where({
                userId: openId,
                articleId: id,
            }).count()
        ]).then(([details, total]) => {
            // 注意使用 where 查询后这里的 details 是个数组
            if (details.length) {
                return {
                    data: {
                        ...details[0],
                        liked: !!total,
                    },
                    message: 'success',
                }
            }
        }).catch( err => {
            console.error(err)
            return Promise.reject({
                data: {},
                message: err.errMsg,
            })
        })
    }