Relations & Subqueries
Hugr automatically generates relation fields based on foreign keys defined with @field_references and @references directives. These fields allow you to fetch related data in a single query, avoiding N+1 query problems and enabling complex data retrieval patterns.
Basic Relations
One-to-One Relations
Fetch a single related record:
query {
orders {
id
total
# One-to-one: each order has one customer
customer {
id
name
email
}
}
}
One-to-Many Relations
Fetch multiple related records:
query {
customers {
id
name
# One-to-many: each customer has many orders
orders {
id
total
created_at
}
}
}
Many-to-Many Relations
Access related records through junction tables:
query {
products {
id
name
# Many-to-many through product_categories junction
categories {
id
name
description
}
}
categories {
id
name
# Reverse many-to-many
products {
id
name
price
}
}
}
Filtering Subqueries
Filter Related Records
Apply filters to subqueries:
query {
customers {
id
name
# Only pending orders
pending_orders: orders(
filter: { status: { eq: "pending" } }
) {
id
status
total
}
}
}
Complex Subquery Filters
query {
customers {
id
name
# High-value recent orders
important_orders: orders(
filter: {
_and: [
{ total: { gte: 1000 } }
{ created_at: { gte: "2024-01-01" } }
{ status: { in: ["pending", "processing"] } }
]
}
) {
id
total
created_at
status
}
}
}
Filter by Nested Relations
query {
customers {
id
name
# Orders containing electronic products
electronics_orders: orders(
filter: {
order_details: {
any_of: {
product: {
category: { name: { eq: "Electronics" } }
}
}
}
}
) {
id
total
}
}
}
Sorting Subqueries
order_by for Subqueries
Sort related records:
query {
customers {
id
name
# Most recent orders first
orders(
order_by: [{ field: "created_at", direction: DESC }]
) {
id
created_at
total
}
}
}
Sort by Related Fields
query {
orders {
id
total
# Order details sorted by product name
order_details(
order_by: [{ field: "product.name", direction: ASC }]
) {
quantity
product {
name
}
}
}
}
nested_order_by
Sort after the join is performed:
query {
customers(limit: 10) {
id
name
orders(
nested_order_by: [{ field: "total", direction: DESC }]
nested_limit: 5
) {
id
total
}
}
}
Difference between order_by and nested_order_by:
order_by- Applied before join (to the orders table)nested_order_by- Applied after join (to orders for each customer)
Limiting Subqueries
Basic Limit
Limit the number of related records:
query {
customers {
id
name
# Only last 5 orders
recent_orders: orders(
order_by: [{ field: "created_at", direction: DESC }]
limit: 5
) {
id
created_at
}
}
}
nested_limit and nested_offset
Control pagination per parent record:
query {
customers {
id
name
# 5 orders per customer, skip first 10
orders(
nested_order_by: [{ field: "created_at", direction: DESC }]
nested_limit: 5
nested_offset: 10
) {
id
created_at
}
}
}
Nested Relations
Multi-Level Nesting
Query through multiple relation levels:
query {
customers {
id
name
orders {
id
total
order_details {
quantity
unit_price
product {
id
name
category {
id
name
}
}
}
shipper {
name
phone
}
}
}
}
Filtering Nested Relations
query {
customers {
id
name
orders(
filter: { status: { eq: "completed" } }
) {
id
status
order_details(
filter: {
product: {
category: { name: { eq: "Electronics" } }
}
}
) {
quantity
product {
name
category {
name
}
}
}
}
}
}
Aggregating Related Data
Single Row Aggregation
Get aggregated statistics for related records:
query {
customers {
id
name
# Aggregate all orders
orders_aggregation {
_rows_count
total {
sum
avg
min
max
}
}
}
}
Filtered Aggregation
Aggregate with filters:
query {
customers {
id
name
# All orders stats
all_orders: orders_aggregation {
_rows_count
total { sum }
}
# Only completed orders
completed_orders: orders_aggregation(
filter: { status: { eq: "completed" } }
) {
_rows_count
total { sum }
}
# High-value orders
large_orders: orders_aggregation(
filter: { total: { gte: 1000 } }
) {
_rows_count
total { sum avg }
}
}
}
Nested Aggregations
Aggregate through multiple levels:
query {
customers {
id
name
# Aggregate orders and their details
orders_aggregation {
_rows_count
total { sum }
# Aggregate order details within orders
order_details {
quantity { sum }
unit_price { avg }
}
}
}
}
Bucket Aggregation on Relations
Group and aggregate related data:
query {
customers {
id
name
# Group orders by status
orders_bucket_aggregation {
key {
status
}
aggregations {
_rows_count
total {
sum
avg
}
}
}
}
}
Complex Bucket Aggregations
query {
customers {
id
name
# Group orders by product category
orders_bucket_aggregation {
key {
order_details @unnest {
product {
category {
name
}
}
}
}
aggregations {
_rows_count
total { sum }
order_details {
quantity { sum }
}
}
}
}
}
Predefined Joins
Custom Join Fields
Use @join directive for custom join conditions:
# Schema definition
extend type customers {
# Spatial join
nearby_stores: [stores] @join(
references_name: "stores"
sql: "ST_DWithin([source.location], [dest.location], 5000)"
)
}
Query custom joins:
query {
customers {
id
name
location
# Stores within 5km
nearby_stores {
id
name
location
}
}
}
Important: Predefined joins created with @join directive cannot be used in filter conditions. To restrict parent records based on join results, use inner: true:
query {
customers {
id
name
# Only customers with nearby stores
nearby_stores(inner: true) {
id
name
}
}
}
This returns only customers who have at least one store within 5km.
Complex Join Conditions
# Schema definition
extend type orders {
similar_orders: [orders] @join(
references_name: "orders"
source_fields: ["customer_id"]
references_fields: ["customer_id"]
sql: """
[source.id] != [dest.id] AND
ABS([source.total] - [dest.total]) < 100
"""
)
}
Query with complex joins:
query {
orders {
id
total
customer_id
# Similar orders from same customer
similar_orders(limit: 5) {
id
total
}
}
}
Use inner: true to include only orders that have similar orders:
query {
orders {
id
total
# Only orders with similar orders
similar_orders(
limit: 5
inner: true
) {
id
total
}
}
}
Aggregating Custom Joins
query {
customers {
id
name
# Aggregate nearby stores
nearby_stores_aggregation {
_rows_count
}
# Group nearby stores by type
nearby_stores_bucket_aggregation {
key {
store_type
}
aggregations {
_rows_count
}
}
}
}
Table Function Joins
Using @table_function_call_join
For functions returning sets of related data, use @table_function_call_join to join function results with object fields.
How Argument Mapping Works:
- The
argsparameter maps function arguments to object fields (not hardcoded values) - Function arguments not in the mapping become field parameters in queries
- Parameter requirements match the function's argument requirements
# Function definition
extend type Function {
get_sensor_readings(
sensor_id: Int!
from_time: Timestamp!
to_time: Timestamp!
): [SensorReading] @function(
name: "fetch_readings"
is_table: true
)
}
# Field definition
extend type sensors {
readings: [SensorReading] @table_function_call_join(
references_name: "get_sensor_readings"
args: {
sensor_id: "id" # Maps sensor_id argument to sensors.id field
}
source_fields: ["id"]
references_fields: ["sensor_id"]
)
}
Result: The readings field requires from_time and to_time parameters (unmapped function arguments):
query {
sensors {
id
name
# from_time and to_time are required because they weren't mapped
readings(
from_time: "2024-01-01T00:00:00Z"
to_time: "2024-01-31T23:59:59Z"
limit: 100
) {
timestamp
value
unit
}
}
}
Limitations
Important: Fields created with @table_function_call_join do not support standard query arguments like filter, order_by, limit, offset, inner, or aggregations (_aggregation, _bucket_aggregation).
For advanced filtering, sorting, and aggregation, use parameterized views instead.
Alternative: Parameterized Views
Parameterized views behave like regular data objects but require arguments, providing full query capabilities:
# Define parameterized view
type sensor_readings_view @view(
name: "sensor_readings_by_period"
sql: """
SELECT * FROM get_sensor_readings([sensor_id], [from_time], [to_time])
"""
) @args(name: "sensor_readings_args", required: true) {
sensor_id: Int! @pk
timestamp: Timestamp! @pk
value: Float!
unit: String!
}
input sensor_readings_args {
sensor_id: Int!
from_time: Timestamp!
to_time: Timestamp!
}
# Create relation to parameterized view
extend type sensors {
readings: [sensor_readings_view] @join(
references_name: "sensor_readings_view"
source_fields: ["id"]
references_fields: ["sensor_id"]
)
}
Now you can use all standard query features:
query {
sensors {
id
name
# Full filtering, sorting, pagination support
high_readings: readings(
args: {
sensor_id: 123
from_time: "2024-01-01T00:00:00Z"
to_time: "2024-01-31T23:59:59Z"
}
filter: { value: { gte: 100 } }
order_by: [{ field: "timestamp", direction: DESC }]
limit: 50
) {
timestamp
value
}
# Aggregations work too
readings_aggregation(
args: {
sensor_id: 123
from_time: "2024-01-01T00:00:00Z"
to_time: "2024-01-31T23:59:59Z"
}
) {
_rows_count
value {
avg
min
max
}
}
}
}
Benefits of parameterized views:
- Full support for
filter,order_by,limit,offset,distinct_on - Aggregation queries (
_aggregation,_bucket_aggregation) - Nested queries with
innerparameter - Can be used in relation filters
- Better performance through query optimization
Inner Joins
By default, Hugr uses LEFT JOINs for subqueries, which means parent records are included even if they have no related records. Use inner: true to switch to INNER JOIN behavior.
Basic INNER JOIN
query {
customers {
id
name
# Only customers with orders - customers without orders are excluded
orders(inner: true) {
id
total
}
}
}
With LEFT JOIN (default):
- Returns all customers
ordersfield is empty array for customers without orders
With INNER JOIN (inner: true):
- Returns only customers who have orders
- Customers without orders are completely excluded from results
Combining with Filters
query {
customers {
id
name
# Only customers with pending orders
pending_orders: orders(
filter: { status: { eq: "pending" } }
inner: true
) {
id
status
total
}
}
}
This returns only customers who have at least one pending order.
Multiple INNER JOINs
query {
customers {
id
name
# Must have orders
orders(inner: true) {
id
# Must have order details
order_details(inner: true) {
product {
name
}
quantity
}
}
}
}
Returns only customers who have orders that have order details.
Use Cases
Find active relationships:
query {
products {
id
name
# Only products with active orders
orders(
filter: { status: { in: ["pending", "processing"] } }
inner: true
) {
id
status
}
}
}
Data validation:
query {
orders {
id
# Ensure order has customer (data integrity check)
customer(inner: true) {
id
name
}
}
}
Note: For one-to-one relations, inner: true excludes records where the relation is NULL.
Self-Referential Relations
Hierarchical Data
# Schema definition
type employees @table(name: "employees") {
id: Int! @pk
name: String!
manager_id: Int @field_references(
references_name: "employees"
field: "id"
query: "manager"
references_query: "subordinates"
)
}
Query hierarchies:
query {
employees {
id
name
# Direct manager
manager {
id
name
}
# Direct reports
subordinates {
id
name
}
}
}
Multi-Level Hierarchies
query {
employees {
id
name
manager {
id
name
# Manager's manager
manager {
id
name
}
}
subordinates {
id
name
# Subordinates' subordinates
subordinates {
id
name
}
}
}
}
Performance Optimization
1. Always Limit Subqueries
# Good
query {
customers {
id
orders(limit: 10) {
id
}
}
}
# Avoid - May fetch thousands of orders per customer
query {
customers {
id
orders {
id
}
}
}
2. Use Aggregations Instead
When you only need counts or statistics:
# Better
query {
customers {
id
orders_aggregation {
_rows_count
total { sum }
}
}
}
# Avoid
query {
customers {
id
orders {
id
total
}
}
}
3. Filter Early
Apply filters at the top level when possible:
# Better - Filter customers first
query {
customers(filter: { country: { eq: "USA" } }) {
id
orders {
id
}
}
}
# Less efficient - Filter after join
query {
orders(filter: { customer: { country: { eq: "USA" } } }) {
id
customer {
id
}
}
}
4. Select Only Required Fields
# Good
query {
customers {
id
orders(limit: 5) {
id
total
}
}
}
# Avoid - Fetching unnecessary fields
query {
customers {
id
name
email
phone
address
orders(limit: 5) {
id
total
status
created_at
updated_at
notes
metadata
}
}
}
5. Use Batch Loading
Hugr automatically batches subqueries to avoid N+1 problems.
Common Patterns
Master-Detail View
query GetOrderDetails($orderId: Int!) {
orders_by_pk(id: $orderId) {
id
order_date
status
total
customer {
name
email
phone
}
order_details {
quantity
unit_price
product {
name
sku
}
}
shipper {
name
phone
}
}
}
Recent Activity
query GetCustomerActivity($customerId: Int!) {
customers_by_pk(id: $customerId) {
id
name
recent_orders: orders(
order_by: [{ field: "created_at", direction: DESC }]
limit: 10
) {
id
created_at
status
total
}
}
}
Dashboard Statistics
query GetDashboard {
customers_aggregation {
_rows_count
}
customers(limit: 10) {
id
name
orders_aggregation {
_rows_count
total { sum }
}
}
orders_bucket_aggregation {
key {
status
}
aggregations {
_rows_count
total { sum }
}
}
}
Next Steps
- Learn about Function Fields to embed function calls in data objects
- See Aggregations for detailed aggregation patterns
- Check Dynamic Joins for ad-hoc query-time joins