When using with in relational queries the `orderBy` function is not typed.

I'm trying to query with relational queries and I've made sure to define the relations correctly. The types work for when I'm selecting columns. I also can't see the where to filter the table.
14 Replies
MAST
MASTOP16mo ago
And this is the error when I hover over the orderBy:
mr_pablo
mr_pablo16mo ago
Try orderBy: (plant, { desc }) => desc(plant.name),
mr_pablo
mr_pablo16mo ago
You have it as orderBy() but its not a function, see here https://orm.drizzle.team/docs/rqb#order-by
MAST
MASTOP16mo ago
Both of these are function so it shouldn't make a difference... they just have different syntax.
mr_pablo
mr_pablo16mo ago
@mast1999 did you try the approach shown in the docs?
MAST
MASTOP16mo ago
Yep It still errors.
Piotrek
Piotrek16mo ago
It won't work because plant is a single entity and not array of those At least I think so I had the same issue with filtering relations
mr_pablo
mr_pablo16mo ago
@mast1999 whats the error if you use "orderBy: (plant, { desc }) => desc(plant.name)," ?
Piotrek
Piotrek16mo ago
Anything besides with, extras and colums won't work on relations that are a single-entity thingy in my experience ¯\_(ツ)_/¯
MAST
MASTOP16mo ago
Yeah, I heard that they removed it to help the typescript perf.
Dan
Dan16mo ago
we didn't remove them, they weren't present from the start. It's a one relation, which always returns either zero on one item, so there is nothing to filter. - but what if I want to filter the result by a column from a plant relation? - you cannot, we don't support filtering by nested columns. If it was a many relation, you could've added where on its level and filter the nested array items, but you cannot filter the outer items by a nested relation's field. so, for example, if you have a users -> cities many-to-one relationship, you can query all the cities with all users and apply filters to either cities, users or both, but you can't filter the list of cities by a user's field. You can filter cities by a city's field and users by a user's field. the easiest workaround here would be to filter the resulting set in the code after you receive it from the DB.
MAST
MASTOP16mo ago
Got it. Thanks for the clarifications! If this isn't pointed out anywhere in the docs I think we should point this out. Others might make the same mistake.
Dan
Dan16mo ago
yes, the relations section will be massively reworked
insane
insane10mo ago
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!
Want results from more Discord servers?
Add your server