In Depth / Sql Expression Entity Field / Sql Relations

Leveraging `sqlRelations` for Advanced SQL-Based Relationships

The sqlRelations API (currently experimental) enhances the way you handle relationships in Remult by enabling SQL-like expressions directly on related entities. This allows for the seamless use of fields from related entities, simplifying complex queries and calculations. Let’s explore its use in a few examples that demonstrate the power of sqlRelations.

Example 1: Adding Customer City to the Order Entity

In many cases, you may want to display information from a related entity, such as a customer’s city, directly within the Order entity. With sqlRelations, you can do this using a straightforward syntax.

5 collapsed lines
import { Entity, Fields, Relations, dbNamesOf } from 'remult'
import { sqlRelations } from 'remult/internals'
import { Customer } from './Customer'

@Entity('orders')
export class Order {
8 collapsed lines
  @Fields.integer()
  id = 0
  @Fields.integer()
  customerId = 0
  @Relations.toOne<Order, Customer>(() => Customer, 'customerId') // This establishes the relation
  customer!: Customer
  @Fields.number()
  amount = 0

  @Fields.string({
    sqlExpression: () => sqlRelations(Order).customer.city, // Retrieve the customer's city directly
  })
  customerCity = ''
}

Explanation

Direct Relation Field: sqlRelations(Order).customer.city pulls the city field from the Customer entity, eliminating the need for a join.
Dynamic Data: The customerCity field within Order automatically updates whenever the related Customer data changes.

To show the number of orders a customer has, you can define a orderCount field in the Customer entity using sqlRelations.

5 collapsed lines
import { Entity, Fields, Relations, dbNamesOf } from 'remult'
import { sqlRelations } from 'remult/internals'
import { Order } from './Order'

@Entity('customers')
export class Customer {
8 collapsed lines
  @Fields.integer()
  id = 0
  @Fields.string()
  name = ''
  @Fields.string()
  city = ''
  @Relations.toMany<Customer, Order>(() => Order, 'customerId')
  orders?: Order[]

  @Fields.integer({
    sqlExpression: () => sqlRelations(Customer).orders.$count(), // Count orders for each customer
  })
  orderCount = 0

14 collapsed lines
  @Fields.integer({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$count({
        amount: { $gt: 50 }, // Apply condition on amount field
      }),
  })
  bigOrderCount = 0

  @Fields.integer<Customer>({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$subQuery((o) => `sum(${o.amount})`), // Aggregate sum of order amounts
  })
  totalAmount = 0
}

Explanation

Counting Relations: sqlRelations(Customer).orders.$count() counts the number of related Order records for each customer.
Efficient Aggregation: This aggregation is done directly in SQL, making it highly efficient for large datasets.

Example 3: Counting Orders with Specific Conditions

Suppose you want to count only the customer’s orders where the amount is over a certain threshold (e.g., greater than 50). You can add a bigOrderCount field to the Customer entity.

5 collapsed lines
import { Entity, Fields, Relations, dbNamesOf } from 'remult'
import { sqlRelations } from 'remult/internals'
import { Order } from './Order'

@Entity('customers')
export class Customer {
14 collapsed lines
  @Fields.integer()
  id = 0
  @Fields.string()
  name = ''
  @Fields.string()
  city = ''
  @Relations.toMany<Customer, Order>(() => Order, 'customerId')
  orders?: Order[]

  @Fields.integer({
    sqlExpression: () => sqlRelations(Customer).orders.$count(), // Count orders for each customer
  })
  orderCount = 0

  @Fields.integer({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$count({
        amount: { $gt: 50 }, // Apply condition on amount field
      }),
  })
  bigOrderCount = 0
7 collapsed lines

  @Fields.integer<Customer>({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$subQuery((o) => `sum(${o.amount})`), // Aggregate sum of order amounts
  })
  totalAmount = 0
}

Explanation

Conditional Counting: This field uses $count with a filter condition to count only orders with an amount greater than 50.
Dynamic Filtering: You can specify any criteria here, providing flexibility for conditional counts within the related entity.

Example 4: Dynamic SQL Aggregation

For advanced calculations, such as summing up the total order amount for each customer, you can use $subQuery to create custom SQL aggregations.

5 collapsed lines
import { Entity, Fields, Relations, dbNamesOf } from 'remult'
import { sqlRelations } from 'remult/internals'
import { Order } from './Order'

@Entity('customers')
export class Customer {
22 collapsed lines
  @Fields.integer()
  id = 0
  @Fields.string()
  name = ''
  @Fields.string()
  city = ''
  @Relations.toMany<Customer, Order>(() => Order, 'customerId')
  orders?: Order[]

  @Fields.integer({
    sqlExpression: () => sqlRelations(Customer).orders.$count(), // Count orders for each customer
  })
  orderCount = 0

  @Fields.integer({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$count({
        amount: { $gt: 50 }, // Apply condition on amount field
      }),
  })
  bigOrderCount = 0

  @Fields.integer<Customer>({
    sqlExpression: () =>
      sqlRelations(Customer).orders.$subQuery((o) => `sum(${o.amount})`), // Aggregate sum of order amounts
  })
  totalAmount = 0
}

Explanation

Custom Aggregations: The $subQuery method allows you to define a custom SQL expression for advanced calculations. Here, it calculates the total order amount for each customer.
Dynamic Syntax: sqlRelations provides flexibility for creating any kind of SQL-based aggregation, allowing you to customize the expression to fit your needs.

Summary of `sqlRelations` Benefits

Efficiency: By generating SQL expressions directly within entity fields, sqlRelations minimizes database calls and performs calculations in SQL, improving performance.
Simplified Code: Directly including related entity fields and aggregations within your entity definitions streamlines code and enhances readability.
Dynamic Aggregations: With $subQuery, you can create complex aggregations based on related data, enabling powerful data insights with minimal effort.

This approach provides a powerful way to manage complex relationships and aggregations in Remult, bringing the flexibility of SQL into the realm of structured, type-safe TypeScript fields.

Edit this page

Getting a field from a relation

Intro & Field-Level Authorization

Files