Ross Posts - Answer Overflow

Ross

•Created by Ross on 8/21/2024 in #help

Return type when fields are conditional

Probably more of a TypeScript question than a Kysely question, but how would I type the output of a wrapper function that allows the conditional specification of fields. E.g.

export function selectActivitiesByActivityId(activityIds: number[], fields: Array<keyof ActivityHistoryTable> = []) {
  const db = createKysely();
  let query = db.selectFrom(ACTIVITIES_TABLE)
  if(fields.length){
    query = query.select(fields);
  } else {
    query = query.selectAll();
  }
  query = query.where('activity_id', 'in', activityIds);
  return query.execute();
}

export function selectActivitiesByActivityId(activityIds: number[], fields: Array<keyof ActivityHistoryTable> = []) {
  const db = createKysely();
  let query = db.selectFrom(ACTIVITIES_TABLE)
  if(fields.length){
    query = query.select(fields);
  } else {
    query = query.selectAll();
  }
  query = query.where('activity_id', 'in', activityIds);
  return query.execute();
}

The inferred type is {} and I'd like the return type to include the fields that were specified in the fields property?

6 replies

KKysely

•Created by Ross on 1/9/2024 in #help

Argument of type 'RawBuilder<unknown>' is not assignable to...

I have a query that looks like this: countQuery.where( 'activityId', 'in', sql(SELECT activityId FROM activities WHERE MATCH(EL_EQ_PRODUCT_DESCRIPTION) AGAINST (+"${value}" IN BOOLEAN MODE)), ); TypeScript is now showing an error that states:

 Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'OperandValueExpressionOrList<Database, "activities", "activityId">'.

This is with Kysely 0.27.0/0.27.1/0.27.2 and TypeScript 5.3.3 Can anyone explain what's going on here please?

9 replies

KKysely

•Created by Ross on 9/11/2023 in #help

Deferred Join

Hi - I'm experimenting with adding deferred join capability (as described here: (https://planetscale.com/learn/courses/mysql-for-developers/examples/deferred-joins) to a limit/offset query. The premise is to apply the order by, limit and offset values to the inner join query but I'm having trouble finding a way syntactically to apply this to the inner join. Do I need to use the sql escape hatch? If so, I'm also struggling with where I'd apply that too! Any pointers appreciated as always, thanks

12 replies

KKysely

•Created by Ross on 9/5/2023 in #help

Subquery on same table

I'm experimenting with how to improve the performance of some queries against a PlanetScale MySQL DB. I have a fulltext index on a field in a table, then some other indexes on various other fields. MySQL can't use two indexes for a query so I'd like to try including the section of the query that uses the fulltext column in a subquery but I'm struggling with the syntax The primary key of the table is activityId. Something like

SELECT * FROM activities
WHERE someCol = '123'
AND someOtherCol = 'abc
AND activityId IN 
(
  SELECT activityId FROM activities
  MATCH(bigTextField) AGAINST ("*some text*" IN BOOLEAN MODE)
)

SELECT * FROM activities
WHERE someCol = '123'
AND someOtherCol = 'abc
AND activityId IN 
(
  SELECT activityId FROM activities
  MATCH(bigTextField) AGAINST ("*some text*" IN BOOLEAN MODE)
)

but I'm struggling with the Kysely syntax to achieve this, can anyone please point me in the right description?

7 replies

KKysely

•Created by Ross on 3/16/2023 in #help

Query with ANDs and ORs - struggling with syntax

I want to be able to create a query that looks like this - i.e. has a first WHERE section that must be true then an either/or but struggling to get the syntax right SELECT * FROM user WHERE (date = '2022-01-01' AND area = 'south') AND ( ( amount = 20 AND status = 'pending' ) OR ( assignee = 'bob' AND amount = 10 ) ) I've put together an example on the playground - any pointers, greatly appreciated! https://wirekang.github.io/kysely-playground/?p=f&i=-NQeDYTzwAp8pwmb5cWT

81 replies

KKysely

•Created by Ross on 3/9/2023 in #help

Extacting where clause generation into dedicated function

I'm building up a query dynamically and need to use some of the logic embedded in where clauses from multiple functions. Therefore, I'd like to encapulate this in a separate function that can be reused but I'm struggling with the type signatures for that function. An example of a query that I'd like to encapsulate is:

query = query.where((qb) => {
  let subQuery = qb.where('activityType', '<>', 'ZSRM');
  subQuery = subQuery.orWhere((qb) =>
    qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', '<=', sevenDaysInFuture),
  );
  subQuery = subQuery.orWhere((qb) => qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', 'is', null));
  return subQuery;
});

query = query.where((qb) => {
  let subQuery = qb.where('activityType', '<>', 'ZSRM');
  subQuery = subQuery.orWhere((qb) =>
    qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', '<=', sevenDaysInFuture),
  );
  subQuery = subQuery.orWhere((qb) => qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', 'is', null));
  return subQuery;
});

If I try to extract this into a function using the IDE it gives me:

function buildContactDatesClause(query: SelectQueryBuilder<From<Database, string>, never, {} & AllSelection<From<Database, string>, never>>, sevenDaysInFuture: Date) {
 ...
}

function buildContactDatesClause(query: SelectQueryBuilder<From<Database, string>, never, {} & AllSelection<From<Database, string>, never>>, sevenDaysInFuture: Date) {
 ...
}

The Database and AllSelection interfaces are not exported by kysely. I assume for the Database interface I can use the type of the row, i.e.

function buildContactDatesClause(query: SelectQueryBuilder<From<ActivityTableItem, string>, never, Record<string, never> & AllSelection<From<ActivityTableItem, string>, never>>, sevenDaysInFuture: Date) {
}

function buildContactDatesClause(query: SelectQueryBuilder<From<ActivityTableItem, string>, never, Record<string, never> & AllSelection<From<ActivityTableItem, string>, never>>, sevenDaysInFuture: Date) {
}

But I'm still stuck with what I need to do with AllSelection which makes me think there's probably a better way to solve this problem. Any thoughts, cheers?

8 replies

Gaming

Programming