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

Question

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?

Answer 1

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.