node mysql连接查询结果只有一个?

Question

现在有posts和tags两个表

posts中一个post可以有多个tag

tags表

视图为

sql:

SELECT posts.*, tags.tag_name
FROM posts
INNER JOIN tags ON posts.post_id = tags.post_id
WHERE posts.post_status = 'publish'
ORDER BY posts.post_date DESC
LIMIT 0,2

node _sql

const getLists = async (page) => {
  let _sql = `SELECT posts.*, tags.tag_name
              FROM posts
              INNER JOIN tags ON posts.post_id = tags.post_id
              WHERE posts.post_status = 'publish'
              ORDER BY posts.post_date DESC
              LIMIT ${(page - 1) * 2},2;`
  return dbquery(_sql)
}

但是在node mysql查询后,结果都是只有一个tag,而不是多个tag?为什么?

结果

Answer 1

好吧，最后在node mysql官方的issues中找了很多例子，结果发现如果直接用一条语句的话，很多查询结果都是返回一个json或object而不是一个array，所以我最后的做法是这样

SELECT
posts.post_id,
posts.post_title,
GROUP_CONCAT(tags.tag_name) as tags
FROM posts
LEFT JOIN tags ON posts.post_id = tags.post_id
GROUP BY posts.post_id
LIMIT 0,10

node _sql

const getLists = async (page) => {
  let _sql = `SELECT
              posts.*,
              GROUP_CONCAT(tags.tag_name) as tags
              FROM posts
              LEFT JOIN tags ON posts.post_id = tags.post_id
              GROUP BY posts.post_id
              LIMIT ${(page - 1) * 10},10`
  return dbquery(_sql)
}

返回的结果

得到了全部tag并转成了字符串类型