K
Kysely12mo ago
decho

Can't get filterWhere to work with joins from another table.

Hey everyone. I am trying to recreate the following SQL (simplified demo):
SELECT
"users"."id",
COUNT(*) FILTER (
WHERE
"predictions"."userId" = "users.id"
) AS "apperances"
FROM
"users"
INNER JOIN "predictions" ON "predictions"."userId" = "users"."id"
INNER JOIN "matches" ON "matchId" = "predictions"."matchId"
SELECT
"users"."id",
COUNT(*) FILTER (
WHERE
"predictions"."userId" = "users.id"
) AS "apperances"
FROM
"users"
INNER JOIN "predictions" ON "predictions"."userId" = "users"."id"
INNER JOIN "matches" ON "matchId" = "predictions"."matchId"
await kysely
.selectFrom('users')
.innerJoin('predictions', 'predictions.userId', 'users.id')
.innerJoin('matches', 'matchId', 'predictions.matchId')
.select('users.id')
.select((eb) => [
eb.fn.countAll().filterWhere('predictions.userId', '=', 'users.id').as('apperances')
])
.execute();
await kysely
.selectFrom('users')
.innerJoin('predictions', 'predictions.userId', 'users.id')
.innerJoin('matches', 'matchId', 'predictions.matchId')
.select('users.id')
.select((eb) => [
eb.fn.countAll().filterWhere('predictions.userId', '=', 'users.id').as('apperances')
])
.execute();
the problem occurs in filterWhere's last parameter and I am getting a type error. Does anyone know how to fix this or if there is a workaround? I am not 100% sure why this error appears, as far as I'm concerned the SQL I am trying to generate is completely valid. Here is a full kyse.link demo: https://kyse.link/?p=s&i=GQxCID0h0CK3Jq2Ki7FB
Solution:
Well, nevermind, I replaced filterWhere with filterWhereRef and now it's working correctly.
Jump to solution
2 Replies
Solution
decho
decho12mo ago
Well, nevermind, I replaced filterWhere with filterWhereRef and now it's working correctly.
Igal
Igal12mo ago
Awesome find!