Knex.js，嵌套'.orWhereExists'到过滤器字段中，以包括男性、女性或两者的选项

Question

我正在使用Knex和Postgres，NodeJS，Express和React。我有一个用户表，一个用户利克斯和一个过滤器表。我被困的地方是性别查询。用户可以在过滤器中定义他们正在寻找男性、女性或两者兼而有之。

如果我使用".orWhereExists"，其他过滤器(例如阻止您成为您已经喜欢/拒绝的返回用户的第二个过滤器(将被忽略。我的直觉告诉我应该以某种方式嵌套性别查询行，然后将它们更改为".orWhereExists"，但我不确定如何。

谢谢大家的帮助。今年刚开始编码并喜欢它，但这个问题一直是一个令人费解的问题

Filters is organized like so 
  table.increments('id')   <-----primary
  table.integer('userid')  <-----foreign
  table.integer('min_age');
  table.integer('max_age');
  table.string('female');
  table.string('male');

app.get('/api/potentials', (req, res) => {
  const cookieid = req.session.id
  console.log("potentials get for id ", cookieid)
    knex('users')
     .select('*')
     .whereNot('users.id', cookieid )
     .whereNotExists(knex.select('*').from('userlikes').whereRaw('userlikes.userid1 = ?', [cookieid]).andWhereRaw('users.id = userlikes.userid2'))
     .whereExists(knex.select('*').from('filters').whereRaw('users.gender = filters.female'))
     .whereExists(knex.select('*').from('filters').whereRaw('users.gender = filters.male'))
     .whereExists(knex.select('*').from('filters').whereRaw('users.age >= filters.min_age'))
     .whereExists(knex.select('*').from('filters').whereRaw('users.age < filters.max_age'))
     .then((result) => {
        console.log("filter result", result)
        res.send(result)
          })
    .catch((err) => {
            console.log("error", err)
          })

Answer 1

> 有一个 Knex 函数.buildermodify()可以适合您的情况。另请参阅：文档链接。

要使用它，您需要创建一个函数，其目的是有条件地将.where或其他类似子句添加到 knex 查询中，并在实际的 knex 查询中，使用 .buildermodify() 调用该函数

文档中的示例：

var withUserName = function(queryBuilder, foreignKey) {
    queryBuilder.leftJoin('users', foreignKey, 'users.id')
        .select('users.user_name');
};
knex.table('articles')
    .select('title', 'body')
    .modify(withUserName, 'articles_user.id')
    .then(function(article) {
          console.log(article.user_name);
    });

将其应用于您获取性别(和年龄(的需求：

/* NEW FUNCTION TO DO THE SPECIAL FILTERING */
function customFiltering(queryBuilder, inputGender, minAge, maxAge) {
    if (inputGender === gender_is_female) { /* you need to fix this */
        /* THIS LINE IS THE SECRET SAUCE TO CONDITIONALLY UPDATE YOUR QUERY */
        /* queryBuilder.<my additional conditional clauses> */
        queryBuilder.whereExists( knex.select('*').from('filters')
                    .whereRaw('users.gender = filters.female'));
    } else if (inputGender === gender_is_male) {
        queryBuilder.whereExists( knex.select('*').from('filters')
                    .whereRaw('users.gender = filters.male'));
    }
    /* ADD MORE CODE HERE for the ages filter - minAge, maxAge */
};
knex('users')
 .select('*')
 .whereNot('users.id', cookieid )
 .whereNotExists( knex.select('*').from('userlikes').whereRaw('userlikes.userid1 = ?', [cookieid]).andWhereRaw('users.id = userlikes.userid2'))
 .modify(customFiltering, inputGender, minAge, maxAge)
 .then((result) => {
    console.log("filter result", result)
    res.send(result)
  })
 .catch((err) => {
    console.log("error", err)
 })

祝你好运！

Answer 2

我通常用普通的SQL编写它，然后尝试将其转换为knex结构。

据我了解，您正在寻找这样的东西

select
  *
from
  users as u
where
    ...
    and (
      (exists select * from filters as f where f.male = u.gender)
      or (exists select * from filters as f where f.female = u.gender)
    )

用knex的话来说，它可以写成

knex('users as u')
  .where('other conditions')
  .where((b) => {
    b
      .whereExists(knex.select('*').from('filters as f').whereRaw('u.gender = f.female'))
      .orWhereExists(knex.select('*').from('filters as f').whereRaw('u.gender = f.male'))
  })

knex有可能将 where 子句分组在括号中。在此处搜索"分组链">

Answer 3

A solution someone offline gave me that worked was to use a "promise" but GaryL's solution above is more inline with what I was looking for. In the event it is helpful to people with similar questions this also worked.
> Blockquote
app.get('/api/potentials', (req, res) => {
  const cookieid = 1//req.session.id
  console.log("potentials get for id ", cookieid)
    Promise.all([
    knex('users')
     .select('filters.min_age','filters.max_age', 'filters.female','filters.male')
     .innerJoin('filters', 'users.id', 'filters.userid')
     .where('users.id',cookieid ),
    knex('users')
    .whereNotExists(knex.select('*').from('userlikes').whereRaw('userlikes.userid1 = ?', [cookieid]).andWhereRaw('users.id = userlikes.userid2'))
      ])
     .then((result) => {
        const[filterCriteria, users] = result
        const [min_age, max_age, female, male] = Object.values(filterCriteria[0])
          res.send(users.filter(user => {
           if((user.age >= min_age) && (user.age <= max_age) && ( (user.gender = female) || (user.gender = male) ||  (user.gender = female) && (user.gender = male) ) ) {
            return user
          }
        }))
      })
    .catch((err) => {
            console.log("error", err)
          })
      })