[v5] How to use "whereHas" with preload

[guntribam]

I have 3 related models and need to filter them while preloading them:

export default class Usuario extends BaseModel {
  public static table = "USUARIO"

	@column({ isPrimary: true })
	id: number 
	@column()
	nome: string 

	@hasMany(() => Conta)
	contas: HasMany<typeof Conta>

}

export default class Conta extends BaseModel {
  public static table = "CONTA"

	@column({ isPrimary: true })
	id: number 
	@column()
	nome: string 
	@column()
	idUsuario: number 

	@belongsTo(() => Usuario)
	usuario: BelongsTo<typeof Usuario>
	@hasMany(() => Lancamento)
	lancamentos: HasMany<typeof Lancamento>

}

export default class Lancamento extends BaseModel {
  public static table = "LANCAMENTO"

	@column({ isPrimary: true })
	id: number 
	@column()
	valor: number 
	@column()
	descricao: string | undefined
	@column()
	idConta: number 
	@column()
	idTipoLancamento: number 
	@column.date()
	data: DateTime 

	@belongsTo(() => Conta)
	conta: BelongsTo<typeof Conta>

}

This is how i am filtering them:

await Usuario.query().whereHas('contas',
          contas => contas.whereHas("lancamentos",
          lancamentos =>lancamentos.where("idTipoLancamento", 1)))

This works great i get the "Usuario"s that contains "idTipoLancamento" equals to 1, but i need that my model "Usuario" comes preloaded with "Conta" and "Lancamento". How can i do that?

[guntribam]

I found a way to work using:

await Usuario.query()
          .preload('contas',contas => contas.preload("lancamentos",
          lancamentos =>lancamentos.where("idTipoLancamento", 1)))
          .whereHas('contas',
          contas => contas.whereHas("lancamentos",
          lancamentos =>lancamentos.where("idTipoLancamento", 1)))

But if anyone know a better way to do it.....

[pk4all]

This is right way. But I think this line lancamentos =>lancamentos.where("idTipoLancamento", 1) not necessary.

[guntribam]

It is necessary. Preloading without it will bring all "Lancamento" and "Conta" to usuario

[remidosol]

You can use afterFind and afterFetch hooks in your Model files. I resolve this problem like that. If you use these hooks, when you fetch a Model row, it always comes preloaded. Then you can filter them in Controller etc using query builder like have you done above.

[remidosol]

Like the following...
You may write these hooks to in your first and second model.

I hope this would be useful.

export default class Usuario extends BaseModel {
  public static table = "USUARIO"

	@column({ isPrimary: true })
	id: number 
	@column()
	nome: string 

	@hasMany(() => Conta)
	contas: HasMany<typeof Conta>

	@afterFind()
	public static async preloadContas (usuario: Usuario) {
		try {
			await usuario.preload('contas');
		} catch (error) {
		console.log(error.message)
		console.log(error.stack)
		}
	}

	@afterFetch()
	public static async preloadContases (usuarios: Usuario[]) {
		try {
			for (let usuario of usuarios) {
				await usuario.preload('contas');
			}
		} catch (error) {
		console.log(error.message)
		console.log(error.stack)
		}
	}

}

export default class Conta extends BaseModel {
  public static table = "CONTA"

	@column({ isPrimary: true })
	id: number 
	@column()
	nome: string 
	@column()
	idUsuario: number 

	@belongsTo(() => Usuario)
	usuario: BelongsTo<typeof Usuario>

	@hasMany(() => Lancamento)
	lancamentos: HasMany<typeof Lancamento>

	@afterFind()
	public static async preloadLancamento (conta: Conta) {
		try {
			await conta.preload('lancamentos');
		} catch (error) {
		console.log(error.message)
		console.log(error.stack)
		}
	}

	@afterFetch()
	public static async preloadLancamentos (contas: Conta[]) {
		try {
			for (let conta of contas) {
				await conta.preload('lancamentos');
			}
		} catch (error) {
		console.log(error.message)
		console.log(error.stack)
		}
	}
}

[pk4all]

This approach is not good , in this you preload data to every query, if you no need to pre load data then?.
Right way is @guntribam.

[remidosol]

This approach is not good , in this you preload data to every query, if you no need to pre load data then?.
Right way is @guntribam.

That's right. But I already specified as

If you use these hooks, when you fetch a Model row, it always comes preloaded.

before.