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

原创
2018/01/04 10:43
阅读数 1.1K

参考地址: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

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部