yayza_
Is this a correct situation to use $castTo (jsonArrayFrom + Left Join)
// snippet from main query:
jsonArrayFrom(
eb
.selectFrom('address')
.leftJoin('stateconst', 'address.stateId', 'stateconst.id')
.leftJoin('contact', 'address.id', 'contact.addressId')
.leftJoin('contacttype', 'contact.typeId', 'contacttype.id')
.whereRef('address.accountId', '=', 'customer.accountId')
.select([
'address.id',
'address.name',
'address.addressName',
'address.address as street',
'address.city',
'stateconst.code as state',
'address.zip',
// contacts
jsonArrayFrom(
eb
.selectFrom('contact')
.leftJoin('contacttype', 'contact.typeId', 'contacttype.id')
.select([
'contact.id',
'contact.contactName as name',
'contact.datus as data',
'contacttype.name as type',
])
.whereRef('contact.addressId', '=', 'address.id')
).as('contacts'),
])
)
.$castTo<
{
id: AddressId;
name: string;
addressName: string;
street: string;
city: string;
state: string;
zip: string;
contacts: {
id: ContactId;
name: string;
data: string;
type: string;
}[];
}[]
>()
.as('addresses'),
// snippet from main query:
jsonArrayFrom(
eb
.selectFrom('address')
.leftJoin('stateconst', 'address.stateId', 'stateconst.id')
.leftJoin('contact', 'address.id', 'contact.addressId')
.leftJoin('contacttype', 'contact.typeId', 'contacttype.id')
.whereRef('address.accountId', '=', 'customer.accountId')
.select([
'address.id',
'address.name',
'address.addressName',
'address.address as street',
'address.city',
'stateconst.code as state',
'address.zip',
// contacts
jsonArrayFrom(
eb
.selectFrom('contact')
.leftJoin('contacttype', 'contact.typeId', 'contacttype.id')
.select([
'contact.id',
'contact.contactName as name',
'contact.datus as data',
'contacttype.name as type',
])
.whereRef('contact.addressId', '=', 'address.id')
).as('contacts'),
])
)
.$castTo<
{
id: AddressId;
name: string;
addressName: string;
street: string;
city: string;
state: string;
zip: string;
contacts: {
id: ContactId;
name: string;
data: string;
type: string;
}[];
}[]
>()
.as('addresses'),
2 replies
KKysely
•Created by yayza_ on 3/26/2025 in #query-showcase
A pagination helper
import { sql, type SelectQueryBuilder, type StringReference } from 'kysely';
export async function paginateQuery<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
options: {
offset?: number;
limit?: number;
countColumn?: StringReference<DB, TB>;
}
) {
const { offset = 0, limit = 10, countColumn = 'id' } = options;
const countResult = await query
.clearSelect()
.clearLimit()
.clearOffset()
.clearGroupBy()
.clearOrderBy()
.select([sql<number>`COUNT(DISTINCT ${sql.ref(countColumn)})`.as('count')])
.$castTo<{ count: number }>()
.executeTakeFirst();
const count = Number(countResult?.count ?? 0);
const rows = await query.limit(limit).offset(offset).execute();
const currentPage = Math.floor(offset / limit) + 1;
const totalPages = Math.ceil(count / limit);
return {
rows,
count,
totalPages,
currentPage,
};
}
import { sql, type SelectQueryBuilder, type StringReference } from 'kysely';
export async function paginateQuery<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
options: {
offset?: number;
limit?: number;
countColumn?: StringReference<DB, TB>;
}
) {
const { offset = 0, limit = 10, countColumn = 'id' } = options;
const countResult = await query
.clearSelect()
.clearLimit()
.clearOffset()
.clearGroupBy()
.clearOrderBy()
.select([sql<number>`COUNT(DISTINCT ${sql.ref(countColumn)})`.as('count')])
.$castTo<{ count: number }>()
.executeTakeFirst();
const count = Number(countResult?.count ?? 0);
const rows = await query.limit(limit).offset(offset).execute();
const currentPage = Math.floor(offset / limit) + 1;
const totalPages = Math.ceil(count / limit);
return {
rows,
count,
totalPages,
currentPage,
};
}
const paginatedResult = await paginateQuery(query, { countColumn: 'product.id', limit: 20, offset: 10 });
const paginatedResult = await paginateQuery(query, { countColumn: 'product.id', limit: 20, offset: 10 });
2 replies
