Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
930 views
in Technique[技术] by (71.8m points)

javascript - How to query similars but not self with Knex.js?

I want to query similar posts but not the post itself included.

Here's my attempt:

export async function getSimilars(slug: string) {
  const excludeThis = await getBySlug(slug)

  const posts = await knex('posts')
    .whereNot({ slug }) // Each post has its own unique slug, used nanoid(11).
    .andWhere({ is_active: true })
    .andWhere({ type: excludeThis.type })
    .orWhere('keywords', 'ilike', `%${excludeThis.keywords}%`)
    .orWhere('title', 'ilike', `%${excludeThis.title}%`)
    .limit(10)
    .orderBy(orderBy)

  // Since posts includes excludeThis post, I have to filter it out here.
  const result = posts.filter(p => p.slug !== slug)
  return result
}

But my attempt query all posts that have similar keywords and title, which includes the post from whereNot. I have to filter it out later.

How do I query the similar of a post without including the post itself?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I suspect your use of orWhere is negating your use of whereNot. Without testing it, I would expect it to generate SQL a bit like the following:

SELECT * FROM posts
  WHERE slug != 'whatever'
    AND ...
    AND ...
    OR ...
    OR ...

Note that there is no particular grouping to these clauses. All of them apply, and because some are OR, there's no requirement that your slug clause evaluates true.

One way around this is to use the Knex solution to grouping: pass a function instead of an object.

const posts = await knex('posts')
  .where({
    is_active: true,
    type: excludeThis.type
  })
  .andWhere(qb =>
    qb
      .where('keywords', 'ilike', `%${excludeThis.keywords}%`)
      .orWhere('title', 'ilike', `%${excludeThis.title}%`)
  )
  .andWhereNot({ slug })

This will generate SQL along the lines of:

SELECT * FROM posts
  WHERE ...
    AND ...
    AND ( ... OR ... )
    AND slug != 'whatever'

Here the OR only applies within the group, so all the clauses outside the group must still evaluate true.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...