where based on nested field

[leognmotta]

I am currently trying to filter a table based on nested field, here is an example:

   const clientsQuery = Client.query({ connection })
      .preload('contacts', (query) => query.where('isPrimary', true).limit(1))
      .preload('taxData', (query) => query.preload('address'))
      .where('contacts.name', 'ilike', `%${search}%`)

but in this case, I get the following error:

missing FROM-clause entry for table "contacts"

How could I possibly approach this with the orm?

Just a quick note on what I am not trying to do, just to make sure I am not miss understood, I want to filter the client table based on contacts result, and not just filter contacts result.

   const clientsQuery = Client.query({ connection })
      .preload('contacts', (query) => query.where('isPrimary', true).limit(1).where('name', 'ilike', `%${search}%`)) // <-- this is not a solution
      .preload('taxData', (query) => query.preload('address'))

export default class Client extends BaseModel {
  @hasMany(() => Contact)
  public contacts: HasMany<typeof Contact>
}

export default class Contact extends BaseModel {
  @belongsTo(() => Client)
  public client: BelongsTo<typeof Client>
}

[thetutlage]

We have a dedicated section for this in the docs https://docs.adonisjs.com/guides/models/relationships#filter-by-relationships. Give it a read and let me know if you have any questions.

A quick note

• The preload method loads the related data
• The has and whereHas methods are used for filtering the top level results based upon a relationship. This is what you need

[leognmotta]

@thetutlage thank you for the help!