Knex中的子查询 [英] Sub-query in Knex

问题描述

我基本上希望在Knex中进行这种查询，但是我不能完全使它起作用:

I'm looking to essentially make this sort of query in Knex, but I can't quite get it to work:

select distinct *
from
(
  select *, 1 as rank from table1 where Word like 'mike'
  union
  select *, 2 as rank from table1 where Word like 'mike%'
  union
  select *, 3 as rank from table1 where Word like '%mike%'
) as X
order by WordOrder

我在此处注意到了类似的问题，并试图听从他们的建议，但似乎无法发现我的问题.错误(或者首先是这样做的正确方法).

I noticed a similar issue here and tried to follow their advice, but can't seem to spot my bug (or if this is even the proper way of doing this in the first place).

var q = DB.knex('Users').select("*", "1 as rank").where("User", "like", query).
    union(function() {
        this.select("*", "2 as rank").where("User", "like", query + "%")
    }).
    union(function() {
        this.select("*", "3 as rank").where("User", "like", query + "%")
    });

DB.knex("Users").distinct("*").from('(' + q.toString() + ') as X').
    orderBy('rank').select().then(...)

如果有帮助，该特定查询将产生以下错误:

If it's any help, that particular query generates the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1`` order by `rank` asc' at line 1, sql: select distinct * from `select` as ``1`` order by `rank` asc, bindings: 

推荐答案

knex的0.6版允许您现在几乎在任何地方使用子查询.在chrome控制台的 http://knexjs.org 中弹出它，您应该会看到它可以满足您的需求

Version 0.6 of knex allows you to use subqueries pretty much anywhere now. Pop this in the chrome console at http://knexjs.org and you should see it gives you what you're looking for

knex.distinct('*').from(function() {
  this.union(function() {
    this.select('*', '1 as rank').from('table1').where('Word', 'like', 'mike')
  }).union(function() {
    this.select('*', '2 as rank').from('table1').where('Word', 'like', 'mike%')
  }).union(function() {
    this.select('*', '3 as rank').from('table1').where('Word', 'like', '%mike%')
  })
  .as('X')
}).orderBy('WordOrder').toString()

这篇关于Knex中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！