Marcel Overdijk Comments - Answer Overflow

Topics

Marcel Overdijk

Explore posts from servers

CDCloudflare Developers KKysely PPrisma HHono 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

•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

•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

•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

•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

•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

•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

•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

•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

•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

•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

•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

•Created by Marcel Overdijk on 9/1/2024 in #help

Using `$dynamic()` to enhance where clause

This an example endpoint: /seasons/:year/driver-standings

  async handle(c: C) {
    const data = await this.getValidatedData<typeof this.schema & ExpandSchema>();
    const year = Number(data.params.year);
    const season = await c.var.loaders.season.load(year);

    if (!season) {
      return c.notFound();
    }

    const query = c.var.f1db
      .select({
        ...getTableColumns(seasonDriverStandingTable),
      })
      .from(seasonDriverStandingTable)
      .where(eq(seasonDriverStandingTable.year, year))
      .$dynamic();

    // Apply query params from the request to the base query.
    await this.applyQueryParams(query);

    // Execute the query.
    const seasonDriverStandings = await query;

    return c.json({
      result: seasonDriverStandings,
    });
  }

  async handle(c: C) {
    const data = await this.getValidatedData<typeof this.schema & ExpandSchema>();
    const year = Number(data.params.year);
    const season = await c.var.loaders.season.load(year);

    if (!season) {
      return c.notFound();
    }

    const query = c.var.f1db
      .select({
        ...getTableColumns(seasonDriverStandingTable),
      })
      .from(seasonDriverStandingTable)
      .where(eq(seasonDriverStandingTable.year, year))
      .$dynamic();

    // Apply query params from the request to the base query.
    await this.applyQueryParams(query);

    // Execute the query.
    const seasonDriverStandings = await query;

    return c.json({
      result: seasonDriverStandings,
    });
  }

22 replies

•Created by Marcel Overdijk on 9/1/2024 in #help

Using `$dynamic()` to enhance where clause

Thx all for the help and especially @alexblokh for offering to look at the code. It's unfortunately not a public repo I can share, but I try to eloborate here; if that's not clear enough, I'm happy to create an isolated example repo. Basics: I'm using HonoJS with Cloudflare Chanfana to expose an API, and I use Drizzle to query the Cloudflare D1 database.

22 replies

•Created by Marcel Overdijk on 9/1/2024 in #help

Using `$dynamic()` to enhance where clause

Sorry @⚡Z.E.U.S⚡ to tag you directly again. But maybe you have an idea?

22 replies

•Created by Marcel Overdijk on 9/1/2024 in #help

Using `$dynamic()` to enhance where clause

https://orm.drizzle.team/docs/dynamic-query-building does explain this exact use case

In the previous ORM versions, when such restrictions weren’t implemented, this example in particular was a source of confusion for many users, as they expected the query builder to “merge” multiple .where() calls into a single condition.

but does not provide an example unfortunately. It only provides an example adding limit/offset.

22 replies

•Created by Marcel Overdijk on 8/26/2024 in #help

[sqlite] How to order by rowid?

I can confirm this worked, thx again @⚡Z.E.U.S⚡ !

4 replies

•Created by Marcel Overdijk on 7/31/2024 in #help

Dynamically adding joins based on relation?

if not , your solution already helps me a lot!

7 replies

•Created by Marcel Overdijk on 7/31/2024 in #help

Dynamically adding joins based on relation?

and the apply that join field toward the qb without the if?

7 replies

•Created by Marcel Overdijk on 7/31/2024 in #help

Dynamically adding joins based on relation?

thank you. it would not be possible to - how must I explain - without the qb to define the leftJoin. e.g.

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id))
  }
};

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id))
  }
};

7 replies