Adonis Database

Hello , guys I build a probject that will use alot select from deferents table I want to know the best away to do this

I’d say use joins and relationships :slight_smile:

Can’t really help much more with so little context :slightly_frowning_face:

1 Like

thanks Macneaky Iam trying to this
res = await Database.select(’*’).from(‘clientes’).whereIn(“id”, Database.from(“contas”).distinct(‘cliente_id’))

Iam trying to use WhereIn with two table or more

I’m quite sure there is better way to do it, but since I’m quite weak in SQL I’d do it in two queries and then figure out how to optimize it later.

const ids = await Database.from('contas').distinct('client_id')
res = await Database.select('*').from('clients').whereIn('id', ids)

But why you don’t use relations?
https://adonisjs.com/docs/4.1/relationships#_has_many

Then you could just do something like

const contasWithClients = await Contas.query().with('clients').fetch()

I want to get all the data from more the table and put in Array like ,

const contasWithClients = await Contas.query().with(‘clients’).fetch()
const paymententCliente= await Contas.query().with(‘clients’).fetch()

and return the , but there user that have conta but have not payment that is my problema s

Now I’m even more confused :sweat_smile:

But create two relations for Contas. clients and payingClients
Then you can query like Contas.query().with('clients').with('payingClients').fetch()
Or just query for all clients and define is_paying or something similar in clients table

Or check for “is_paying” while doing query

Contas.query()
  .with('clients', (builder) => {
    builder.with('payments') // Get paying clients in here
  })