Where based on relationship condition

[popovicieduard]

Is there a way to query records based on relationship condition?

I have an Account and AccountProfile model, account hasOne profile, how could I query accounts based on profile status attribute ?

In v4 this could be done with:

return this.related('profile', 'status', status)

[thetutlage]

Where in the docs you found this.related('profile', 'status', status)?

[popovicieduard]

In 4.1 I was using adonis-lucid-filter package that had this syntax. https://github.com/jericopulvera/adonis-lucid-filter

[popovicieduard]

it is using whereHas relation

  related (relation, column, operator, value = null) {
    if (value === null) {
      value = operator
      operator = '='
    }
    return this.$query.whereHas(relation, (builder) => {
      builder.where(column, operator, value)
    })
  }

[thetutlage]

Ohh. whereHas is landed yesterday only. You can upgrade lucid and give it a try (Docs are not out yet).

npm i @adonisjs/lucid@alpha

and then use it as follows

await User.query().whereHas('profile', (query) => {
  query.where(column, value)
})

Shameless plug: Follow us on twitter and stay updated :) https://twitter.com/adonisframework/status/1290913193590751232?s=20

[popovicieduard]

Just found out about this, already updated my lucid to 8.3.1, thank you.

[iammrsea]

I don't think you can do that, the reason being that Lucid likely runs the query sequentially. That is, it fetches the account model first and secondly fetches the profile model. So, your condition will only be run against the profile model while fetching it. What you could do is to fetch the account models and filters the result at the application level

[popovicieduard]

Hmm, that won't work in my case, if I filter the results at application level it will mess up with my pagination meta data.

[iammrsea]

Too bad, but why not move the status field back to account model if it's that important to you? I had to do something similar when I had same problem and realized that filtering would also mess with my pagination

[popovicieduard]

Well, I tought about moving everything from profile to account, but this way,in my case, it would add up more complexity to my app, and I would like to keep my models as simple and clean as possible.