insane
insane
PostsComments
DTDrizzle Team
Created by MAST on 8/10/2023 in #help
When using with in relational queries the `orderBy` function is not typed.
Hi. Just wanted to share an easy workaround using the sql operator while we wait for the rework! Suppose we have two entities. Order and each order has many Products related. Let's say i want to sort all orders by their first product's name (You can also do aggregations, and other stuff but im gonna go with this for the sake of the example). The code would look something like this for PostgreSQL: 
db.query.orders.findMany({
  columns: {
    id: true,
  },
  with: {
    products: {
      id: true,
      name: true
    }
  }
  orderBy: desc(sql`(SELECT DISTINCT ${products}."name" FROM ${products} WHERE ${products}."order_id" = ${orders.id} LIMIT 1)`);
});
db.query.orders.findMany({
  columns: {
    id: true,
  },
  with: {
    products: {
      id: true,
      name: true
    }
  }
  orderBy: desc(sql`(SELECT DISTINCT ${products}."name" FROM ${products} WHERE ${products}."order_id" = ${orders.id} LIMIT 1)`);
});
Note: There's an important DB performance issue here, since we're using a correlated query. In case this workaround does not work for you, please consider using a custom sql query until the team finishes the rework!
23 replies