Adonis-Js : Get a specifc number of Entities in relationship with another Entity

Question

I need your help in Adonis-Js for loading an an entity with relationships. Here is my problem :

I have two entities :

• Contact (belongsTo Contactgroup)
• Contactgroup (hasMany Contact)

I want to make a request to get the list of Contactgroup with some Contact in it, here is an exemple, this code gives me all Contactgroup with all Contact related to them. :

    let ContactgroupList = await Contactgroup.query()
        .where('profile_id', auth.user.id)
        .with('contact')
        .fetch();

In my case I just want to get all Contactgroup with only some Contact related to them (for exemple a maximum of 3 Contact for each Contactgroup found), but when I use the code below :

    let ContactgroupList = await Contactgroup.query()
        .where('profile_id', auth.user.id)
        .with('contact', (builder) => {
          builder.pick(3)
        })
        .fetch();

I get only the first 3 contact related to the first Contactgroup, exemple :

if Contactgroup[0] have X Contact -> it shows an array of the first 3 contact of it, and if Contactgroup[1] have X Contact -> it will show an empty array.

How can I get 3 contact for each Contactgroup ?

I hope my explanation is clear, and thanks in advance for your help !

Answer 1

a friend of mine gave me the solution with using the eagerLoadQuery :

let ContactgroupList = await Contactgroup.query()
  .where('profile_id', auth.user.id)
  .with('contact', (builder) => {
    builder.eagerLoadQuery((relationQuery, foriegnKey, groups) => {
      relationQuery
        .from('contacts as c')
        .whereRaw('(select count(*) from `contacts` as c1 where c.contactgroup_id = c1.contactgroup_id AND c.id < c1.id) < 3')
        .whereIn(`c.${foriegnKey}`, groups)
        .orderBy('c.id', 'desc')
    })
  })
  .fetch()

see also this post for more informations : Link