withCount on belongsToMany relationship


#1

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')
                .pivotTable('followers')
}

Then the inverse relation:

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

The followers table has: user_id and follower_id fields


#2

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


#3

@virk

return await User.query()
                .where('username', params.username)
                .withCount('followers')
                .withCount('following')
                .firstOrFail()

SQL:

'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 ?'

#4

And as per you the count is always 0?


#5

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
        }
    }
}

#6

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


#7

Sure

User:

table.increments()
table.string('name').notNullable()
table.string('username', 80).notNullable().unique()
table.string('email', 254).notNullable().unique()
table.string('password', 60).notNullable()
table.string('profile_pic').nullable()
table.timestamps()

Follower:

table.increments()
table.integer('user_id').unsigned().notNullable()
table.integer('follower_id').unsigned().notNullable()
table.timestamps()

#8

Thanks, will look into it :slight_smile:


#9

Thanks, really appreciate!


#10

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


#11

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


#12

Has this been tagged?


#13

Yes released a while ago


#14

Oh! alright, thanks!!!