K
Kysely12mo ago
mike

coalesce in where statement

I've found examples and documentation for coalesce in select however I still struggle how to use it in where statement.
const published = new Date();
const tagId = -1;

const raw = kysely
.selectFrom('Article')
.selectAll()
.where(({ cmpr, selectFrom, and }) =>
and([
cmpr(
'id',
'in',
selectFrom('Article as Article2')
.select('id')
.where(({ and, cmpr, ref }) =>
and([
cmpr('Article2.published', '<=', published),
cmpr(
'Article2.AreaId',
'=',
ref('Article.AreaId'),
),
cmpr(
coalesce(
ref('Article2.TagId'),
tagId, // Argument of type '-1' is not assignable to parameter of type 'ReferenceExpression<Database, keyof Database>'.ts(2345)
),
'=',
coalesce(
ref('Article.TagId'),
tagId, Argument of type '-1' is not assignable to parameter of type 'ReferenceExpression<Database, keyof Database>'.ts(2345)
),
),
]),
)
.orderBy('Article2.published', 'desc')
.limit(1),
),
]),
);
const published = new Date();
const tagId = -1;

const raw = kysely
.selectFrom('Article')
.selectAll()
.where(({ cmpr, selectFrom, and }) =>
and([
cmpr(
'id',
'in',
selectFrom('Article as Article2')
.select('id')
.where(({ and, cmpr, ref }) =>
and([
cmpr('Article2.published', '<=', published),
cmpr(
'Article2.AreaId',
'=',
ref('Article.AreaId'),
),
cmpr(
coalesce(
ref('Article2.TagId'),
tagId, // Argument of type '-1' is not assignable to parameter of type 'ReferenceExpression<Database, keyof Database>'.ts(2345)
),
'=',
coalesce(
ref('Article.TagId'),
tagId, Argument of type '-1' is not assignable to parameter of type 'ReferenceExpression<Database, keyof Database>'.ts(2345)
),
),
]),
)
.orderBy('Article2.published', 'desc')
.limit(1),
),
]),
);
3 Replies
koskimas
koskimas12mo ago
You need to use val to give a value to coalesce. It interprets its inputs as column references by default https://kyse.link/?p=s&i=cK0N1Y0xm8BpX0hKBC70
mike
mike12mo ago
So I don’t need to use ref to reference the column, but I need to use Val to reference the value right?
koskimas
koskimas12mo ago
Yes
Want results from more Discord servers?
Add your server
More Posts