Add support for filtering data based upon relations

[thetutlage]

One should be able to filter top level rows by adding clauses on relationships. For example:

Fetch all posts where comments are greater than 3.

yield Post.query().has('comments', '>', 3).fetch()

Required Method

1. has
2. whereHas
3. doesntHave
4. whereDoesntHave
5. withCount

[thetutlage]

Original Thread

by @KungWaz

It would be nice to be able to filter the top level rows when querying models with relations.

If I have this structure:

office: {
  orders: [{
    items: [{
      status: 'p'
    }, {
      status: 'c'
    }]
  },{
    items: [{
      status: 'd'
    }]
  }]
  },{
    items: [{
      status: 'd'
    }]
  }]
  }]]
}

I want to get this result:

office: {
  orders: [{
    items: [{
      status: 'p'
    }, {
      status: 'c'
    }]
  }]]
}

But at the moment I can only accomplish this:

office: {
  orders: [{
    items: [{
      status: 'p'
    }, {
      status: 'c'
    }]
  },{
    items: []
  }]
  },{
    items: []
  }]
  }]]
}

I use the query builder with scoping:

const office = yield Office
  .query()
  .with('orders.items')
  .where('id', officeId)
  .scope('orders.items', (builder) => {
    builder.whereNot('status','d')
  })
  .first()

It would be nice to use a whereHas function here to be able to only get Orders that have OrderItems.

This request is related to this question on SO:
http://stackoverflow.com/questions/41679272/is-it-possible-to-do-a-nested-query-builder-in-adonisjs-with-lucid/41715041#41715041