where_has not working

[yubarajshrestha]

Users Table

id	name
1	John
2	Jane

Roles Table

id	name	slug
1	Manager	manager
2	Editor	editor

RoleUser Pivot Table

id	role_id	user_id
1	2	1
2	2	2

users = User.where_has(
    'roles',
    lambda q: q.where('slug', 'editor')
).get()

Expected Output

[
    {
        "id": "1",
        "name": "John",
    }, {
        "id": "2",
        "name": "Jane",
    }
]

Current Output
Expected Output

[
    {
        "id": "1",
        "name": "John",
    }
]

Originally posted by @yubarajshrestha in #522 (comment)

[josephmancuso]

could have swore I tested this exact scenario but I'll replicate this again 🤔

[yubarajshrestha]

could have swore I tested this exact scenario but I'll replicate this again 🤔

SELECT * FROM "users" WHERE EXISTS (SELECT "role_user".* FROM "role_user" WHERE "role_user"."id" = "users"."id" AND "role_user"."id" IN (SELECT "roles"."id" FROM "roles" WHERE "roles"."slug" = 'editor'))

I can see that you're comparing role_user.id with users.id which is incorrect.

[josephmancuso]

hmm. correct query should be

SELECT * FROM "users" WHERE EXISTS (SELECT "role_user".* FROM "role_user" WHERE "role_user"."user_id" = "users"."id" AND "role_user"."role_id" IN (SELECT "roles"."id" FROM "roles" WHERE "roles"."slug" = 'editor'))

[josephmancuso]

Fixed in #529