Filtering and Relations
In this article, we'll discuss several relevant techniques for one-to-many relations. Consider the following scenario where we have a customer entity and an Orders entity.
We'll use the following entities and data for this article.
import { Entity, Field, Fields, remult, Relations } from 'remult'
@Entity('customers')
export class Customer {
@Fields.autoIncrement()
id = 0
@Fields.string()
name = ''
@Fields.string()
city = ''
@Relations.toMany(() => Order)
orders?: Order[]
}
@Entity('orders')
export class Order {
@Fields.autoIncrement()
id = 0
@Relations.toOne(() => Customer)
customer!: Customer
@Fields.number()
amount = 0
}
Use Case in this article
Let's say that we want to filter all the orders of customers who are in London.
Let's have a look at the different options to achieve this.
Option 1 - Use In Statement
Add the where
inline to the find
method.
console.table(
await repo(Order).find({
where: {
customer: await repo(Customer).find({
where: {
city: 'London',
},
}),
},
}),
)
Option 2 - Prepare entity
We can refactor this to a custom filter that will be easier to use and will run on the backend
import { Filter } from 'remult'
@Entity('orders', { allowApiCrud: true })
export class Order {
//...
static filterCity = Filter.createCustom<Order, { city: string }>(
async ({ city }) => ({
customer: await repo(Customer).find({ where: { city } }),
}),
)
}
And then we can use it:
console.table(
await repo(Order).find({
where: Order.filterCity({
city: 'London',
}),
}),
)
Option 3 - Prepare entity (SQL)
We can improve on the custom filter by using the database's in statement capabilities:
import { SqlDatabase } from 'remult'
@Entity('orders', { allowApiCrud: true })
export class Order {
//...
static filterCity = Filter.createCustom<Order, { city: string }>(
async ({ city }) =>
SqlDatabase.rawFilter(
({ param }) =>
`customer in (select id from customers where city = ${param(city)})`,
),
)
}
We can also reuse the entity definitions by using dbNamesOf
and filterToRaw
import { dbNamesOf } from 'remult'
@Entity('orders', { allowApiCrud: true })
export class Order {
//...
static filterCity = Filter.createCustom<Order, { city: string }>(
async ({ city }) => {
const orders = await dbNamesOf(Order)
const customers = await dbNamesOf(Customer)
return SqlDatabase.rawFilter(
async ({ filterToRaw }) =>
`${orders.customer} in
(select ${customers.id}
from ${customers}
where ${await filterToRaw(Customer, { city })})`,
)
},
)
}
Option 4 - sqlExpression field
@Entity('orders', { allowApiCrud: true })
export class Order {
//...
@Fields.string<Order>({
sqlExpression: async () => {
const customer = await dbNamesOf(Customer)
const order = await dbNamesOf(Order)
return `(
select ${customer.city}
from ${customer}
where ${customer.id} = ${order.customer}
)`
},
})
city = ''
}
- This adds a calculated
city
field to theOrder
entity that we can use to order by or filter
console.table(
await repo(Order).find({
where: {
city: 'London',
},
}),
)
Side Note
In this option, city
is always calculated, and the sqlExpression
is always executed. Not a big deal, but it's woth mentioning. (Check out Option 5 for a solution)
Option 5 - Dedicated entity
export class OrderWithCity extends Order {
@Fields.string<Order>({
sqlExpression: async () => {
const customer = await dbNamesOf(Customer)
const order = await dbNamesOf(Order)
return `(
select ${customer.city}
from ${customer}
where ${customer.id} = ${order.customer}
)`
},
})
city = ''
}
Like this, in your code, you can use OrderWithCity
or Order
depending on your needs.
TIP
As OrderWithCity
extends Order
, everything in Order
is also available in OrderWithCity
🎉.