Drizzle Team•3mo ago

Using `$dynamic()` to enhance where clause

I have a function like: export const applyFilter = (qb: SQLiteSelect, paging: Paging, filter: string | undefined) => { .. } which adds limit and offset and also where using qb.where(..). This works great, but now I have the use case the qb received already contains a where clause. But the qb.where(..) overwrites it unfortunately. Is there a way to merge them instead, if the qb already has a where?

8 Replies

Marcel OverdijkOP•3mo ago

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.

Drizzle ORM - Dynamic query building

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Marcel OverdijkOP•3mo ago

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

Angelelz•3mo ago

This has been a point of friction for such a long time. You might get something out of this issue. I share a way you could do that there: https://github.com/drizzle-team/drizzle-orm/issues/1644

GitHub

[FEATURE]: Merge multiple calls to where when in dynamic mode · I...

Describe what you want It would be great if multiple calls to the where method could be merged with an and when the query was in dynamic mode. This would be fantastic for scenarios where you want t...

alexblokh•3mo ago

hey, @Marcel Overdijk our filters API designed specifically for such cases you can compose filter set and pass around filtersets as opposed to tossing around query and mutating one if you can share a minimal repro - I can tell you exactly how it should be done with Drizzle PS: We're very aware of the restriction and build $dymaic escape and we know we should not tell you how to write code, etc. But we truly believe passing around query builder is an antipattern there's always a clean and concise way to write dynamic queries, we're happy to help you compose one based on your business demands

Marcel OverdijkOP•3mo ago

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. 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,
    });
  }

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))). The other important part is the this.applyQueryParams(query); . This is a generic method that receives the qb: SQLiteSelect argument. 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. 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);
  }

 
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);
  }
};

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));
};

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. @alexblokh off course I would like to remove anti-patterns from code base, so I'm very interested in your opinion. 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.

alexblokh•3mo ago

@Marcel Overdijk

alexblokh•3mo ago

this is how you meant to use Drizzle queries with dynamic parts

Marcel OverdijkOP•3mo ago

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. but of course thx for sharing this! @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

Gaming

Programming

Using `$dynamic()` to enhance where clause