TypeScript db.table函数代码示例

export async function syncPostToGrapher(postId: number): Promise<string|undefined> {
    const rows = await wpdb.query("SELECT * FROM wp_posts WHERE ID = ? AND post_status != 'trash'", [postId])

    const matchingRows = await db.table(Post.table).where({ id: postId })
    const existsInGrapher = !!matchingRows.length

    const wpPost = rows[0]
    const postRow = wpPost ? {
        id: wpPost.ID,
        title: wpPost.post_title,
        slug: wpPost.post_name.replace(/__/g, '/'),
        type: wpPost.post_type,
        status: wpPost.post_status,
        content: wpPost.post_content,
        published_at: wpPost.post_date_gmt === "0000-00-00 00:00:00" ? null : wpPost.post_date_gmt,
        updated_at: wpPost.post_modified_gmt === "0000-00-00 00:00:00" ? "1970-01-01 00:00:00" : wpPost.post_modified_gmt    
    } as Post.Row : undefined

    await db.knex().transaction(async t => {
        if (!postRow && existsInGrapher) {
            // Delete from grapher
            await t.table(Post.table).where({ 'id': postId }).delete()
        } else if (postRow && !existsInGrapher) {
            await t.table(Post.table).insert(postRow)
        } else if (postRow && existsInGrapher) {
            await t.table(Post.table).where('id', '=', postRow.id).update(postRow)
        }
    })

    const newPost = (await Post.select('slug').from(db.table(Post.table).where({ id: postId })))[0]
    return newPost ? newPost.slug : undefined
}

    // Return object representing datapackage.json for this dataset
    async toDatapackage(): Promise<any> {
        // XXX
        const sources = await Source.find({ datasetId: this.id })
        const variables = await db.table(Variable.table).where({ datasetId: this.id }) as Variable.Row[]
        const tags = await db.query(`SELECT t.id, t.name FROM dataset_tags dt JOIN tags t ON t.id=dt.tagId WHERE dt.datasetId=?`, [this.id])

        const initialFields = [
            { name: "Entity", type: "string" },
            { name: "Year", type: "year" }
        ]

        const dataPackage = {
            name: this.name,
            title: this.name,
            id: this.id,
            description: (sources[0] && sources[0].description && sources[0].description.additionalInfo)||"",
            sources: sources.map(s => s.toDatapackage()),
            owidTags: tags.map((t: any) => t.name),
            resources: [{
                path: `${this.name}.csv`,
                schema: {
                    fields: initialFields.concat(variables.map(v => ({
                        name: v.name,
                        type: "any",
                        description: v.description,
                        owidDisplaySettings: v.display
                    })))
                }
            }]
        }

        return dataPackage
    }

async function main() {
    try {
        const categoriesByPostId = await wpdb.getCategoriesByPostId()

        const tagsByPostId = await wpdb.getTagsByPostId()
        const postRows = await wpdb.query("select * from wp_posts where (post_type='page' or post_type='post') AND post_status != 'trash'")
    
        for (const post of postRows) {
            const categories = categoriesByPostId.get(post.ID)||[]

            let tagNames = categories.map(t => decodeHTML(t))

            const matchingTags = await Tag.select('id', 'name', 'isBulkImport').from(
                db.table(Tag.table).whereIn('name', tagNames).andWhere({ isBulkImport: false })
            )

            const existingTags = await Tag.select('id').from(
                db.table(Tag.table)
                    .join('post_tags', { 'post_tags.tag_id': 'tags.id' })
                    .where({ 'post_tags.post_id': post.ID })
            )

            const tagIds = matchingTags.map(t => t.id).concat(existingTags.map(t => t.id))
            // if (matchingTags.map(t => t.name).includes(post.post_title)) {
            //     tagIds.push(1640)
            // }


            // const matchingTags = await Tag.select('id', 'name', 'isBulkImport').from(
            //     db.knex().from(Tag.table).whereIn('name', tagNames).andWhere({ isBulkImport: false })
            // )
            // let tagIds = matchingTags.map(t => t.id)
            // if (matchingTags.map(t => t.name).includes(post.post_title)) {
            //     tagIds.push(1640)
            // }
            await Post.setTags(post.ID, _.uniq(tagIds))
        }
    } finally {
        await wpdb.end()
        await db.end()
    }
}

 export async function bySlug(slug: string): Promise<Post.Row|undefined> {
     return Post.rows(await db.table('posts').where({ slug: slug }))[0]
 }

api.get('/posts/:postId.json', async (req: Request, res: Response) => {
    const postId = expectInt(req.params.postId)
    const post = await db.table(Post.table).where({ id: postId }).select('*').first() as Post.Row
    return camelCaseProperties(post)
})

async function getPostTags(postId: number) {
    return await db.table("post_tags")
        .select("tags.id", "tags.name")
        .where({ post_id: postId })
        .join("tags", "tags.id", "=", "post_tags.tag_id") as Tag[]
}