node服务器knex查询mysql，select子查询

2019独角兽企业重金招聘Python工程师标准>>>

参考地址：http://knexjs.org/

原始mysql语句：

SELECT
	a.*, (
		SELECT
			b. NAME
		FROM
			kind b
		WHERE
			b.id = a.kind
	) AS kind_name,
	(
		SELECT
			c.url
		FROM
			music c
		WHERE
			c.id = a.music
	) AS music_url
FROM
	card a
WHERE
	a. SHOW = 1

转化为knex语句：

module.exports = async ctx => {
  ctx.body = await knex('card')
    .join('kind', 'card.kind', '=', 'kind.id')
    .join('music', 'card.music', '=', 'music.id')
    .select('card.*', 'kind.name as kind_name', 'music.url as music_url')
    .where('show', 1)
}

编译后的sql语句：

SELECT
	`card`.*,
	`kind`.`name` AS `kind_name`,
	`music`.`url` AS `music_url`
FROM
	`card`
INNER JOIN `kind` ON `card`.`kind` = `kind`.`id`
INNER JOIN `music` ON `card`.`music` = `music`.`id`
WHERE
	`show` = 1

 

转载于:https://my.oschina.net/kaikai1/blog/1601538