withCount on belongsToMany relationship


I defined a belongsToMany relationship which went well as I can query and eager load the relationships using the with method. Now the problem is I want to get the count of a relation using withCount method but it not working as expected as it keeps returning 0 even though the relation is more than 0.

I’m using a single model (for both models) with a custom pivot table. Here is how I defined the model:

followers () {
    return this.belongsToMany('App/Models/User', 'user_id', 'follower_id')

Then the inverse relation:

following () {
    return this.belongsToMany('App/Models/User', 'follower_id', 'user_id')

The followers table has: user_id and follower_id fields


Can u share the actual query and the output SQL query as well.

Simply set debug: true inside config/database.js under the connection settings and it will print the SQL query to the terminal



return await User.query()
                .where('username', params.username)


'select *, (select count(*) from `users` inner join `followers` on `users`.`id` = `followers`.`follower_id` where users.id = followers.user_id) as `followers_count`, (select count(*) from `users` inner join `followers` on `users`.`id` = `followers`.`user_id` where users.id = followers.follower_id) as `following_count` from `users` where `username` = ? limit ?'


And as per you the count is always 0?


Yeah. Here is a sample response:

"data": {
	"id": 2,
	"name": "Gary Silva",
	"username": "vupri",
	"email": "ban@gemfujeg.io",
	"password": "$2a$10$3etLlG4ckVxT8YxnNEz3Q.Dbw9nHcnnWDp5ADK1INiX0E4K3Zf9iu",
	"profile_pic": null,
	"created_at": "2017-11-06 22:27:54",
	"updated_at": "2017-11-06 22:27:54",
	"__meta__": {
		"followers_count": 0,
		"following_count": 0


Weird. Lemme go ahead and try to reproduce the code. Can u share your migrations as well ( to speed up the process )




table.string('username', 80).notNullable().unique()
table.string('email', 254).notNullable().unique()
table.string('password', 60).notNullable()




Thanks, will look into it :slight_smile:


Thanks, really appreciate!


Fixed here https://github.com/adonisjs/adonis-lucid/commit/8b51561e56a72ede8966cf5de3d1ba77892018cd will be out soon


Thanks, looking out to when it will be out. I really appreciate your time.


Has this been tagged?


Yes released a while ago


Oh! alright, thanks!!!