Add conditions to Query


#1

Hi.

I want to know if Lucid / database allows to add conditions to a query. For Example

async getProducts(params, response){

let myproducts = await Product.query()

if (params.color){
myproducts.where('color',params.color)
}

myproducts.fetch()

response.send(myproducts)

}

#2

Take a look at the documentation. It’s described at the very first beginning.

https://adonisjs.com/docs/4.0/query-builder

const query = Database.table('product')

if (params.color) {
  query.where('color', params.color)
}

#3

Hi @kayyyy

Yes, but that’s all the information available. As you can see, they don’t use await, so when you have to use it? This works in Lucid Models?


#4

You would use await if you are querying the database.
So const query = await Database.table('product')

async/await is not really related to Adonis. It’s part of ES8 or 7 / what ever :smiley:

So your code would look like this:

async getProducts(params){

    let myproducts = await Database.table('products')

    if (params.color){
      query.where('color',params.color)
    }

    return myproducts;

}

#6

Because your query is wrong. Take a look at the documentation about where clauses

const users = await Database.from('users').where('id', 1)
return users

#7

@kayyyy, no chance to use it with Lucid then?


#8

This work for me…

async getDepots({ request, response }) {
        // Get vars
        const qV = request.get()

        // Set query
        const query = Depot.query()
        query.withCount('employees as total_employees')

        query.withCount('images as total_images')

        query.with('images', imagesQuery => imagesQuery.orderBy('order', 'ASC'))

        query.with('createdby', createdbyQuery =>
            createdbyQuery.select('id', 'firstname', 'lastname')
        )

        query.with('lockedby', lockedbyQuery =>
            lockedbyQuery.select('id', 'firstname', 'lastname')
        )

        query.with('updatedby', updatedbyQuery =>
            updatedbyQuery.select('id', 'firstname', 'lastname')
        )

        query.with('deletedby', deletedbyQuery =>
            deletedbyQuery.select('id', 'firstname', 'lastname')
        )

        if (qV.deleted === '1') {
            query.whereNull('depots.deleted_at')
        }

        if (qV.deleted === '2') {
            query.whereNotNull('depots.deleted_at')
        }

        if (qV.search) {
            query.where(function() {
                this.where('depots.depot_name', 'LIKE', '%' + qV.search + '%')
                this.orWhere('depots.corp_name', 'LIKE', '%' + qV.search + '%')
            })
        }

        // Run query
        const depots = await query.fetch()

        // Set response
        response.status(200).json({
            depots
        })
    }

#9

Maybe in this way will work for you @jorgeyoma (this an example adapt it to your scenario)

const data = ModelName.query()

           data.where('id', 1)

  const res  = await data.fetch()
  return res

As you can see

  • first I give to variable data access to the ModelName
  • after in the same line I make use of query() method
  • after I can use data variable and make use of where() method
  • after with a new res variable I assign the await usage
  • in the same line of res I access to the data var and the fetch method to get records
  • finally I return the res var

This works for me


#10

@ShadowPaz
Thanks, but it doesn’t work. The sql throws an error.


#12

Hi @ShadowPaz

Thanks for your help.

I was writing

await data.fetch()
return data

instead of

const res  = await data.fetch()
return res

now it works!