如何用knex重写原始SQL查询? [英] How to rewrite raw sql query with knex?

问题描述

我只是开发中的新手，这是我第一次使用knex.

I'm just new in development and it's the first time I'm using knex.

问题: 我有一个原始的SQL查询，它可以正常工作.现在，我正在尝试使用knex进行此查询.为了了解一切工作原理，我想:

Problem: I have a raw SQL query, which works properly. Now, I'm trying to use knex for this query. In order to learn how everything works I'd like to:

1. 使用knex.raw重写查询
2. 使用knex查询构建器重写查询.

有人可以帮我吗?顺便说一句，我正在使用Postgres和Next.js. 通过运行下面的代码，我得到"UnhandledPromiseRejectionWarning:错误:预期为1个绑定，看到为0".我不知道问题是否在这里:

Could someone help me with that? Btw, I'm using Postgres and Next.js. By running the code below I get "UnhandledPromiseRejectionWarning: Error: Expected 1 bindings, saw 0". I don't know if the problem is somewhere here:

typeof req.query.word ==='string'吗? [req.query.word]:req.query.word)

typeof req.query.word === 'string' ? [req.query.word] : req.query.word)

...所以我已经尝试重写它(使用[])，但是没有用.这是代码:

... so I already tried to rewrite it (used [ ]), but it didn't work. Here's the code:

const getTranslation = (req, res) => {
  const params =
    typeof req.query.word === 'string'
      ? req.query.word
      : req.query.word.map((_, index) => `$${index + 1}`);
    console.log(req.query.word);

  knex.raw(
    `SELECT "Translation", "Words" FROM "Dictionary" WHERE "Words" IN (${
      typeof req.query.word === 'string' ? '($1)' : params.join(',')
    })`,
    typeof req.query.word === 'string' ? [req.query.word] : req.query.word)
    
      .then((error, result) => {
        const wordArray = typeof req.query.word === 'string' ? [req.query.word] : req.query.word;
        if (error) {
          throw error; 
        } 
        const wordOrder = req.query.word;
        result.rows.sort((row1, row2) => {
          return wordOrder.indexOf(row1.Words) - wordOrder.indexOf(row2.Words);
        });
        res.status(200).json(result.rows);
    }
  );
};

我尝试过的事情: 我尝试使用以下简单查询来检查配置是否正常运行.我认为是的:终端(网络)显示状态为200的请求，并且我在控制台中看到了数据...

What I've tried: I tried to use the following simple query to check if the configuration is working properly. And I think it does: the terminal (network) shows a request with status 200 and I see the data in the console...

const getTranslation = (req, res) => {
  knex.select("Words", "Translation").from("Dictionary")
      .then(rows =>
        rows.map(row => {
          console.log(row)
        }))
} 

谢谢！

推荐答案

您的查询应如下所示:

const results = await knex('Dictionary')
  .columns(['Translation', 'Words'])
  .whereIn('Words', req.query.word); // assumes that `req.query.word` is array with strings/numbers

这篇关于如何用knex重写原始SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！