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?
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.