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


0 votes
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}%`)

  // 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:

  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')
    is_active: true,
    type: excludeThis.type
  .andWhere(qb =>
      .where('keywords', 'ilike', `%${excludeThis.keywords}%`)
      .orWhere('title', 'ilike', `%${excludeThis.title}%`)
  .andWhereNot({ slug })

This will generate SQL along the lines of:

  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