Marcel Overdijk
Marcel Overdijk
Explore posts from servers
CDCloudflare DevelopersKKyselyPPrismaHHonoDTDrizzle Team
PostsComments
PPrisma
Created by Marcel Overdijk on 9/12/2024 in #help-and-questions
Order by `rowid`?
Bump. Anyone an idea if this is possible?
3 replies
KKysely
Created by Marcel Overdijk on 9/9/2024 in #help
Dynamic query parts based on user input
Is that a serious answer, or being sarcastic? Nevertheless, I'm looking to see if this is possible with Kylesy or not...
5 replies
KKysely
Created by Marcel Overdijk on 9/9/2024 in #help
Dynamic query parts based on user input
e.g. I did a similar experiment with Drizzle ORM, and there I did something like: 
const countryTableAlias = aliasedTable(countryTable, 'coun');
const countryJoin = leftJoin(countryTableAlias, eq(countryTableAlias.id, constructorTable.countryId));

const countryContinentTableAlias = aliasedTable(continentTable, 'coun_cont');
const countryContinentJoin = leftJoin(countryContinentTableAlias, eq(countryContinentTableAlias.id, countryTableAlias.continentId));

queryFields : {
  'name': { col: constructorTable.name },
  'country.code': { col: countryTableAlias.alpha2Code, join: countryJoin },
  'country.continent.code': { col: countryContinentTableAlias.code, join: [countryJoin, countryContinentJoin] },
}
const countryTableAlias = aliasedTable(countryTable, 'coun');
const countryJoin = leftJoin(countryTableAlias, eq(countryTableAlias.id, constructorTable.countryId));

const countryContinentTableAlias = aliasedTable(continentTable, 'coun_cont');
const countryContinentJoin = leftJoin(countryContinentTableAlias, eq(countryContinentTableAlias.id, countryTableAlias.continentId));

queryFields : {
  'name': { col: constructorTable.name },
  'country.code': { col: countryTableAlias.alpha2Code, join: countryJoin },
  'country.continent.code': { col: countryContinentTableAlias.code, join: [countryJoin, countryContinentJoin] },
}
which if fully type safe.
5 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
@alexblokh I experimented a bit with you suggestion to not pass the qb(anti pattern) . I'm now returning the filter and sort expressions from a helper, similar as the pagination informations. However for joins this I have a question for which I Created a separate post: https://discord.com/channels/1043890932593987624/1282601056244858910
22 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
I'm now using this: 
    const qb = c.var.mydb
      .selectFrom('customer')
      .selectAll('customer');

    const { query } = await this.applyQueryParams(qb)

    const customers = await query.execute();
    const qb = c.var.mydb
      .selectFrom('customer')
      .selectAll('customer');

    const { query } = await this.applyQueryParams(qb)

    const customers = await query.execute();
9 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
thx @koskimas I already was using that (hopefully temporary) when I got the error: 
  async applyQueryParams<DB, TB extends keyof DB, O>(qb: SelectQueryBuilder<DB, TB, O>): Promise<{ qb: SelectQueryBuilder<DB, TB, O> }> {
    ..
    return { qb };
  }
  async applyQueryParams<DB, TB extends keyof DB, O>(qb: SelectQueryBuilder<DB, TB, O>): Promise<{ qb: SelectQueryBuilder<DB, TB, O> }> {
    ..
    return { qb };
  }
and then using it like: 
qb = (await this.applyQueryParams(qb)).qb;
qb = (await this.applyQueryParams(qb)).qb;
and that works. I must admit, it doesn't really look pretty 😉 my other alternative is to split retrieving the async request data and enhancing the query. that way I can make it non async
9 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
https://github.com/kysely-org/kysely/pull/748#issuecomment-2332608963
9 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
https://github.com/kysely-org/kysely/issues/693#issuecomment-2332606627
9 replies
KKysely
Created by Marcel Overdijk on 9/5/2024 in #help
Error: don't await SelectQueryBuilder instances directly.
I found this related issue as well: https://github.com/kysely-org/kysely/issues/838 but it does not give a solution, or maybe there is none, and an async function can't return a query builder?
9 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
but of course thx for sharing this!
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
yes, that's possible, but it would mean that I have to do that in every endpoint: - retrieve page and pageSize arguments - retrieve and parse sort and filter arguement - and apply it to the query instead of calling just 1 method in all my endpoints. that would mean a lot of duplicated code, which also needs to be tested.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
The most easy solution I see is, to pass the base where to my this.applyQueryParams(query, baseWhere) and then merge them and set them on the query builder there.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
@alexblokh off course I would like to remove anti-patterns from code base, so I'm very interested in your opinion.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
I'm using this pattern with this.applyQueryParams(query) in many endpoints to automatically enhance the query with pagination, sorting and filtering. It's really nice as I don't have to take care of that anymore in each endpoint, but in 1 general place and consistent everywhere.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
and the applyFilter is similar, although it's a bit more complicated 😉 
export const applyFilter = (qb: SQLiteSelect, filter: string | undefined, queryOptions: QueryOptions) => {
  if (!filter) {
    return;
  }

  // Destructure the query options.
  const { fields = {} } = queryOptions;

  // Parse the RSQL query filter.
  const expression = parse(filter);

  const traverse = (node: ExpressionNode): SQL | undefined => {
    .. // returns where
  };

  // Traverse the parsed RSQL expression and apply the results to the query.
  qb.where(traverse(expression));
};
export const applyFilter = (qb: SQLiteSelect, filter: string | undefined, queryOptions: QueryOptions) => {
  if (!filter) {
    return;
  }

  // Destructure the query options.
  const { fields = {} } = queryOptions;

  // Parse the RSQL query filter.
  const expression = parse(filter);

  const traverse = (node: ExpressionNode): SQL | undefined => {
    .. // returns where
  };

  // Traverse the parsed RSQL expression and apply the results to the query.
  qb.where(traverse(expression));
};
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
 
export const applyPagination = (qb: SQLiteSelect, page: number, pageSize: number) => {
  qb.limit(pageSize);
  qb.offset((page - 1) * pageSize);
};
 
export const applyPagination = (qb: SQLiteSelect, page: number, pageSize: number) => {
  qb.limit(pageSize);
  qb.offset((page - 1) * pageSize);
};
export const applySort = (qb: SQLiteSelect, sort: string | undefined, queryOptions: QueryOptions) => {
  if (!sort) {
    return;
  }

  const orderBy = .. // determine sort, e.g. asc(someField)

  // Apply the order by columns.
  if (orderBy) {
    qb.orderBy(...orderBy);
  }
};
export const applySort = (qb: SQLiteSelect, sort: string | undefined, queryOptions: QueryOptions) => {
  if (!sort) {
    return;
  }

  const orderBy = .. // determine sort, e.g. asc(someField)

  // Apply the order by columns.
  if (orderBy) {
    qb.orderBy(...orderBy);
  }
};
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
This is the actual code: 
  async applyQueryParams(qb: SQLiteSelect) {
    const data = await this.getValidatedData<PaginationSchema & SortSchema & FilterSchema>();
    const page = data.query[QueryParam.PAGE] || PAGINATION_DEFAULT_PAGE;
    const pageSize = data.query[QueryParam.PAGE_SIZE] || this.queryOptions.pagination?.defaultPageSize || PAGINATION_DEFAULT_DEFAULT_PAGE_SIZE;
    const sort = data.query[QueryParam.SORT];
    const filter = data.query[QueryParam.FILTER];

    applyFilter(qb, filter, this.queryOptions);
    applySort(qb, sort, this.queryOptions);
    applyPagination(qb, page, pageSize);
  }
  async applyQueryParams(qb: SQLiteSelect) {
    const data = await this.getValidatedData<PaginationSchema & SortSchema & FilterSchema>();
    const page = data.query[QueryParam.PAGE] || PAGINATION_DEFAULT_PAGE;
    const pageSize = data.query[QueryParam.PAGE_SIZE] || this.queryOptions.pagination?.defaultPageSize || PAGINATION_DEFAULT_DEFAULT_PAGE_SIZE;
    const sort = data.query[QueryParam.SORT];
    const filter = data.query[QueryParam.FILTER];

    applyFilter(qb, filter, this.queryOptions);
    applySort(qb, sort, this.queryOptions);
    applyPagination(qb, page, pageSize);
  }
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
this applyQueryParams function has access to the HTTP query params, and based on what is provided it will apply/enhance the query with the supplied HTTP query params.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
The other important part is the this.applyQueryParams(query); . This is a generic method that receives the qb: SQLiteSelect argument.
22 replies
DTDrizzle Team
Created by Marcel Overdijk on 9/1/2024 in #help
Using `$dynamic()` to enhance where clause
What's important to mention is that the "base" query needs a where with year is equal to the path param from the endpoint (.where(eq(seasonDriverStandingTable.year, year))).
22 replies