CODEWITHSUNDEEP
knex.js
Salem
Salem

Reputation: 1162

Sub-query in 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:

Upvotes: 17

Views: 20970

Answers (2)

tgriesser
tgriesser

Reputation: 2808

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()

Upvotes: 36

clay
clay

Reputation: 6017

Edit: This answer refers to older version of knex. See other answer.

When I do this, I use the knex.raw functionality. You can put any raw SQL in there. Like this:

 var selectRaw = "SUM( IF( "+ table.id +" = 1, "+ table.value +", 0.00 )) as customAlias";
 query.column( knex.raw( selectRaw ) );

You might even be able to build the query with knex, and then just use the .toString() method to fill knex.raw. My example was not part of their API (those IFs...).

Upvotes: 3

Related Questions