[BUG] Cannot use "whereHas" inside "where" callback with "paginate" on lucid models


#1

Hey everybody, how ya doin?
first of all, please forgive me for my bad english.

I have a case where i need to group my where clause, using where with callback .
inside the callback, i defined data filtering of a other related models, so it becames like this:

const user = use('App/Models/User')

const getUser = await User.query().with('role').with('biodata').where('is_deleted', 0)
  .where(function () {
    this.orWhere('username', 'like', '%string%')
    this.orWhere('status', 'like', '%string%')

    this.orWhereHas('biodata', (builder) => {
      builder.orWhere('fullname', 'like', '%string%')
      builder.orWhere('address', 'like', '%string%')
    })

    this.orWhereHas('role', (builder) => {
      builder.orWhere('role_name', 'like', '%string%')
    })
  })
  .fetch()

console.log( getUser.toJSON() )

It Works, and the results’s just as I expected.

but it fails when I tried using .paginate().
it fails to recognize the .orWhereHas as a function

TypeError 

this.orWhereHas is not a function

here’s the complete code when I tried using .paginate() :

const user = use('App/Models/User')

const getUser = await User.query().with('role').with('biodata').where('is_deleted', 0)
  .where(function () {
    this.orWhere('username', 'like', '%string%')
    this.orWhere('status', 'like', '%string%')

    this.orWhereHas('biodata', (builder) => {
      builder.orWhere('fullname', 'like', '%string%')
      builder.orWhere('address', 'like', '%string%')
    })

    this.orWhereHas('role', (builder) => {
      builder.orWhere('role_name', 'like', '%string%')
    })
  })
  .paginate(5)

console.log( getUser.toJSON() )

I already found an alternative method of not using paginate,
which is using .clone() and re-run two queries,
one to do a .getCount(), and the other to get the data results, with .limit(),

But I’m hoping to be able to use a more simple .paginate() to achieve this.


#2

Would you mind changing the query as follows.

.where(function (builder) {
  builder.orWhereHas(...)
})

#3

tried it, but no luck.

const getUser = await User.query().with('role').with('biodata').where('is_deleted', 0)
      .where(function (builder) {

        builder.orWhere('username', 'like', '%string%')

        builder.orWhereHas('biodata', (builder) => {
          builder.orWhere('fullname', 'like', '%string%')
          builder.orWhere('address', 'like', '%string%')
        })

        builder.orWhereHas('role', (builder) => {
          builder.orWhere('label', 'like', '%string%')
        })
      })
      .paginate(5)

    return getUser.toJSON()
builder.orWhereHas is not a function

What makes me wonder is, if i use just “.fetch()”,
it works, and the result sets is what i expected.
it’s just doesn’t work when using “.paginate()


#4

Yeah that’s weird. Can you please share a simple repo with the bare minimum code to reproduce this issue. I will be happy to solve it ASAP


#5

hey Virk.
sorry for the late reply.

Not sure you’ve meant by bare minimum,
but I hope this would might help you identify the problem.
I just created a repo:


I included an .sql file for the db sample i used.

could you please identify my issue?


#6

I’ve opened a new Issue on Lucid git repository regarding this.
hope it could help you indentify the problem :slightly_smiling_face: