I am using knex and objection.js to work with my postgres database. I have this query involving three different tables and different possible filters. The problem I have is when I do a left join and query with .whereRaw, I get the following error: error: missing FROM-clause entry for table "publication" If I use .where instead, it works. However I need the whereRaw to make the filter not case-sensitive.
Reader.query(Reader.knex())
.where('Reader.id', '=', readerId)
.eager('[tags, publications.[tags, attributions]]')
.modifyEager('publications', builder => {
if (filter.title) {
const title = filter.title.toLowerCase()
builder.where('Publication.name', 'like', `%${title}%`)
// builder.whereRaw(
// 'LOWER(name) LIKE ?',
// '%' + filter.title.toLowerCase() + '%'
// )
}
if (filter.attribution || filter.author) {
builder.leftJoin(
'Attribution',
'Attribution.publicationId',
'=',
'Publication.id'
)
}
if (filter.author) {
builder
.where('Attribution.normalizedName', '=', author)
.andWhere('Attribution.role', '=', 'author')
}
if (filter.attribution) {
builder.where(
'Attribution.normalizedName',
'like',
`%${attribution}%`
)
if (filter.role) {
builder.andWhere('Attribution.role', '=', filter.role)
}
}
orderBuilder(builder)
builder.limit(limit)
builder.offset(offset)
})
The commmented out .whereRaw query is the one I want to make work. What is different between the .where and .whereRaw that would cause one to work and not the other?
