Is there a way to do conditional query in AdonisJS 4.1.0?

I’m following the query builder of AdonisJs: https://adonisjs.com/docs/4.1/query-builder#_introduction

I am trying to do something similar to the example posted under their ‘Conditional Queries’ subheading:

const query = Database.table('users')
if (username) {
  query.where('username', username)
}

However, this sends an error on my backend.

Is there a way to do conditional where query? In some cases, I need to do have the where clause whereas in other cases based on a boolean value, I need all the data so where is not needed.

I notice that the ‘query’ variable (in the above example) is chainable only when it’s chained to the Database class. When I save that as a variable, it throws an error (i.e. query.where(...) throws the error).

My current solution is to have 9 possibilities for 3 boolean conditions and each of the queries will be created from Database class. I may have more than 3 conditions soon though.

Is there a way to do conditional where queries?

P.S: I do know I can have multiple fields in one where clause, although this is unrelated to my question. i.e. .where({ field1: true, field2: false})

I’m wondering if there’s any shortcuts for this with:

 const query = Database.table('users')
 .where({ field1: boolean ? var1 : NOT_NULL,
 field2: boolean ? var2 : NOT_NULL, })

where var1 & var2 are what’s used in the where clause if boolean condition is true. In the falsy scenario, the where clause shouldn’t be applied. The fields are of type string and integer. I’ve tried having it as null, empty object and !null with no luck

2 Likes

Hello :wave:

Can you share the full error output?

Are you using await to run the query?

And welcome to our community :grinning:

2 Likes

@CrBast thanks. Yes, I was using await on the constant declaration line without knowing it. It makes sense that it doesn’t work. I realized my mistake about 2 days ago (a few hours after creating my post), removing await solved it.
I’m grateful for the support and may ask more questions in the future, :slight_smile:

Regards.

2 Likes