How to get count


#1

Hi, everyone
I need a help to get entire table contents with some addition in it

I have a blacklists table like this

id description reported_id sender_id created_at updated_at
1 some descriptions 5 4 2018-08-05 08:59:46 2018-08-05 08:59:46
2 some descriptions 5 3 2018-08-05 08:59:46 2018-08-05 08:59:46


I want to get blacklists with the count of the sender
I can use distinct like this

let { page, limit } = request.post()
let pagination = await Blacklist
  .query()
  .with('reported')
  .distinct('reported_id')
  .paginate(page, limit)
let blacklists = pagination.toJSON().data

It returned something like this

{
    "message": "Blacklist fetched",
    "blacklists": [
        {
            "reported_id": 5,
            "reported": {
                "id": 5,
                "username": "owner",
                "email": "owner@gmail.com",
                "level": "owner",
                "created_at": "2018-08-03 08:38:06",
                "updated_at": "2018-08-03 11:43:24"
            }
        }
    ]
}

But how can I add the sender count in it?
I need it will return something like this

{
    "message": "Blacklist fetched",
    "blacklists": [
        {
            "reported_id": 5,
            "reported": {
                "id": 5,
                "username": "owner",
                "email": "owner@gmail.com",
                "level": "owner",
                "created_at": "2018-08-03 08:38:06",
                "updated_at": "2018-08-03 11:43:24"
            },
            "sender_count": 2
        }
    ]
}

Thanks


#2

Currently I can achieve it by using this loop

for (const blacklist of blacklists) {
  blacklist.sender_count = await Blacklist
    .query()
    .where('reported_id', blacklist.reported_id)
    .getCount('sender_id')
}

I wonder how I can achieve it by using just one query


#3

Here’s the docs https://adonisjs.com/docs/4.1/relationships#_counts