sql 查询片段

新建文件 post.provider.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/**
 * 查询片段
 */
export const sqlFragment = {
  user: `
    JSON_OBJECT(
        'id', user.id,
        'name', user.name
       ) as user
    `,
  leftJoinUser: `
    LEFT JOIN user
    ON user.id = post.userId
    `,
  totalComments: `
    (
        SELECT
          COUNT(comment.id)
        FROM
          comment
        WHERE
          comment.postId = post.id
      ) as totalComents
    `,
  leftJoinOneFile: `
      LEFT JOIN LATERAL (
          SELECT *
          FROM file
          WHERE file.postId = post.id
          ORDER BY file.id DESC
          LIMIT 1
      ) AS file ON post.id = file.postId
    `,
  file: `
      CAST(
          IF(
              COUNT(file.id),
              GROUP_CONCAT(
                  DISTINCT JSON_OBJECT(
                      'id',file.id,
                      'width',file.width,
                      'height',file.height
                  )
              ),
              null
          ) AS JSON
      ) AS file
    `,
  leftJoinTag: `
      LEFT JOIN
        post_tag ON post_tag.postId = post.id
      LEFT JOIN
        tag ON post_tag.tagId = tag.id
    `,
  tags: `
      CAST(
        IF(
          COUNT(tag.id),
          CONCAT(
              '[',
                GROUP_CONCAT(
                    DISTINCT JSON_OBJECT(
                        'id',tag.id,
                        'name',tag.name
                    )
                ),
              ']'
          ),
          NULL
        ) AS JSON
      ) AS tags
    `,
};

用的时候：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
/**
 * 获取内容列表
 */
export interface GetPostsOptionsFilter {
  name: string;
  sql?: string;
  param?: any;
}

interface GetPostOptions {
  sort?: string;
  filter?: GetPostsOptionsFilter;
}
export const getPosts = async (options: GetPostOptions) => {
  const { sort, filter } = options;

  // sql参数
  let params: Array<any> = [];

  // 设置sql参数
  if (filter.param) {
    params = [filter.param, ...params];
  }

  const statement = `
    SELECT
      post.id,
      post.title,
      post.content,
      ${sqlFragment.user},
      ${sqlFragment.totalComments},
      ${sqlFragment.file},
      ${sqlFragment.tags}
    FROM post
    ${sqlFragment.leftJoinUser}
    ${sqlFragment.leftJoinOneFile}
    ${sqlFragment.leftJoinTag}
    WHERE ${filter.sql}
    GROUP BY post.id
    ORDER BY ${sort}
  `;
  const [data] = await connection.promise().query(statement, params);

  return data;
};