Hot to use custom SQL functions in the WHERE clause

Question

I'm trying to make a where clause with custom functions applied to the columns using Knex.js.

Suppose I have a table named tableName with columns named col1, col2, col3 and a function f that receives as parameter something that is the same type of the things that are in col1 and col2.

I also have two variables named var1 and var2 (defined beforehand) that are the same type of the thing returned by f. I tried some ways.

Example 1:

let rows = knexClient("tableName").whereRaw('f(?) <= ${var1} AND f(?) >= ${var2}', [col1, col2]).then((rows) => {
  for (row of rows) {
    console.log('${row["col1"]} ${row["col2"]} ${row["col3"]}');
  }
}).catch((err) => {
  console.log(err);
  throw err;
});

This gives the following error:

ReferenceError: col1 is not defined.

Example 2:

let rows = knexClient("tableName").whereRaw("f(col1) <= ? AND f(col2) >= ?", [var1, var2]).then((rows) => {
  for (row of rows) {
    console.log('${row["col1"]} ${row["col2"]} ${row["col3"]}');
  }
}).catch((err) => {
  console.log(err);
  throw err;
});

This gives the following error:

SQLITE_ERROR: no such column: col1] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

What is the right way to do it? I have searched around and saw some people doing things similar to my first try here. But it didn't work for me.

Tomalak · Accepted Answer · 2021-08-13T08:57:31.060

1

You can't bind column names dynamically (i.e. using ? placeholders), that only works for values.

The following:

var var1 = 10, var2 = 20;

knex("tableName")
    .whereRaw("f(col1) <= ?", var1)
    .whereRaw("f(col2) => ?", var2)
    .select();

results in generated SQL like this:

select
  *
from
  tableName
where
  f(col1) <= 10
  and f(col2) => 20

If you have variables that contain the target column names, you need to format them in yourself:

var col1 = "some_col", col2 = "other_col";
var var1 = 10, var2 = 20;

knex("tableName")
    .whereRaw(`f(${col1}) <= ?`, var1)
    .whereRaw(`f(${col2}) => ?`, var2)
    .select();

which produces

select
  *
from
  tableName
where
  f(some_col) <= 10
  and f(other_col) => 20

edited Aug 13 '21 at 08:57

answered Aug 13 '21 at 08:40

Tomalak

332,285
67
532
628

Thanks! My case is the first one that you mentioned. Now it's producing the right query but I'm getting the error [Error: select * from `tableName` where f(col1) <= var1 and f(col2) >= var2 - SQLITE_ERROR: no such function: f] { errno: 1, code: 'SQLITE_ERROR' } – joaopfg Aug 13 '21 at 08:58
Does f need to be set somewhere inside the knex query to be used ? – joaopfg Aug 13 '21 at 09:00
@python123 I was under the impression that `f` was a function that *you had defined in SQLite*. If that is not the case, then of course you need to define that function first. You can't call Javascript functions from your SQL query. – Tomalak Aug 13 '21 at 09:03
I see. What is the syntax to define a custom function using Knex.js ? I can't find the docs about it – joaopfg Aug 13 '21 at 09:06
@python123 You can't define a custom function through Knex.js. All that Knex does is generate an SQL string, and send that SQL string to a DB engine (such as SQLite) for execution. These things are completely separate. SQLite supports [creating custom functions](https://www.sqlite.org/appfunc.html) but there is no way to do that dynamically from Node.js. You would either have to [load an extension](https://github.com/mapbox/node-sqlite3/wiki/Extensions), which requires writing C and compiling it, or replace the `f` with an SQL expression - depending on what `f` does, this might be possible. – Tomalak Aug 13 '21 at 09:20
I see, thanks. In my case f converts a date to a string in the format "YYYY-MM-DD". col1 and col2 contain dates. Is there some way to contour the use of this function in SQL ? – joaopfg Aug 13 '21 at 09:33
@python123 SQLite has a [comprehensive list of date and time functions](https://www.sqlite.org/lang_datefunc.html), you can use them. You don't need your custom `f`. You will probably need `strftime()` and 15 minutes of reading the documentation and experimenting. – Tomalak Aug 13 '21 at 09:35
Thanks a lot! I will give a look. I will also choose your answer as the accepted one. – joaopfg Aug 13 '21 at 09:37
@python123 I'd recommend using [DB Browser for SQLite](https://sqlitebrowser.org/) as a tool to experiment with the SQL, and when you have figured it out, you will know what expression to use in `.whereRaw()`. – Tomalak Aug 13 '21 at 09:43
Also, if your date columns columns in SQLIte are formatted as ISO dates (`YYYY-MM-DD hh:nn:ss`) then you don't need a function at all. You can directly compare them against values formatted in the same way. `.where('somedate', '>=', var1)`. This would improve query performance considerably. If your date columns in SQLIte are *not* formatted this way, you should very strongly consider converting them. Every other date format is inferior. – Tomalak Aug 13 '21 at 09:47
@python123 ...and while you're at it, you should make sure that all dates in your database are strictly UTC. Convert them to UTC before storing them, and only convert them to local dates when displaying them to users. – Tomalak Aug 13 '21 at 10:07
Thanks, @Tomalak, you gave a great introduction for a beginner like me working with databases – joaopfg Aug 13 '21 at 11:33
@python123 One thing to know about SQLite is that there is no DATE/DATETIME datatype. SQLite accepts those keywords, but date values are always saved as strings (TEXT). That's why it's important to pick the right date format when you save data to your tables, and to stick to that format. – Tomalak Aug 13 '21 at 12:16
1

Yes, @Tomalak. I'm currently using a function to correct the time zone offset and then I store everything as strings in the format "YYYY-MM-DD HH:MM;SS" (in the database and in my variables). It works well and now I can query just using .where('somedate', '>=', var1), as you suggested – joaopfg Aug 13 '21 at 12:45

Hot to use custom SQL functions in the WHERE clause

1 Answers1