Querying Relations

Relations allow you to work with related data across collections. Learn how to load, filter, and manipulate relationships in your queries.

Overview

When you define relations between collections, you can query them using:

Prop

Type

Loading Relations

include

Use include to load related records:

const posts = await collections.posts.findMany({
  include: {
    author: true  // Load the author relation
  }
})

// Result type:
// {
//   id: number
//   title: string
//   authorId: number
//   author: {
//     id: number
//     name: string
//     email: string
//   }
// }[]

Nested Relations

Load deeply nested relations:

const posts = await collections.posts.findMany({
  include: {
    author: {
      include: {
        profile: true  // Load author's profile
      }
    },
    comments: {
      include: {
        author: true  // Load comment authors
      }
    }
  }
})

Choose specific fields from related records:

const posts = await collections.posts.findMany({
  include: {
    author: {
      select: {
        name: true,
        email: true
        // Don't load other fields
      }
    }
  }
})

Relation Types

Filtering Relations

Find records based on their relations:

// Find posts by a specific author
const posts = await collections.posts.findMany({
  where: {
    author: {
      email: { equals: 'author@example.com' }
    }
  }
})

// Find posts with specific category
const posts = await collections.posts.findMany({
  where: {
    categories: {
      some: {
        slug: { equals: 'typescript' }
      }
    }
  }
})

Relation Filter Operators

Prop

Type

some - At Least One Match

// Posts with at least one published comment
const posts = await collections.posts.findMany({
  where: {
    comments: {
      some: {
        status: { equals: 'published' }
      }
    }
  }
})

// Posts with at least one "typescript" tag
const posts = await collections.posts.findMany({
  where: {
    tags: {
      some: {
        name: { equals: 'typescript' }
      }
    }
  }
})

every - All Must Match

// Posts where ALL comments are published
const posts = await collections.posts.findMany({
  where: {
    comments: {
      every: {
        status: { equals: 'published' }
      }
    }
  }
})

// Posts with exactly these tags (no other tags)
const posts = await collections.posts.findMany({
  where: {
    tags: {
      every: {
        name: { in: ['typescript', 'nodejs'] }
      }
    }
  }
})

none - None Must Match

// Posts with NO deleted comments
const posts = await collections.posts.findMany({
  where: {
    comments: {
      none: {
        status: { equals: 'deleted' }
      }
    }
  }
})

// Posts without "deprecated" tag
const posts = await collections.posts.findMany({
  where: {
    tags: {
      none: {
        name: { equals: 'deprecated' }
      }
    }
  }
})

Advanced Relation Queries

Filtering and Loading Together

const posts = await collections.posts.findMany({
  where: {
    // Filter by relation
    author: {
      status: { equals: 'active' }
    }
  },
  include: {
    // Load the relation
    author: {
      select: {
        name: true,
        email: true
      }
    }
  }
})

Counting Relations

const posts = await collections.posts.findMany({
  include: {
    _count: {
      select: {
        comments: true
      }
    }
  }
})

// Result includes:
// {
//   id: number
//   title: string
//   _count: {
//     comments: number
//   }
// }

Ordering by Relations

// Order posts by comment count
const posts = await collections.posts.findMany({
  include: {
    _count: {
      select: {
        comments: true
      }
    }
  },
  orderBy: {
    _count: {
      comments: 'desc'
    }
  }
})

Limiting Included Relations

Limit the number of related records loaded:

const posts = await collections.posts.findMany({
  include: {
    comments: {
      take: 5,  // Only load 5 most recent comments
      orderBy: {
        createdAt: 'desc'
      }
    }
  }
})

Filtering Included Relations

Apply filters to loaded relations:

const posts = await collections.posts.findMany({
  include: {
    comments: {
      where: {
        status: { equals: 'published' }  // Only load published comments
      },
      orderBy: {
        createdAt: 'desc'
      }
    }
  }
})

Real-World Examples

Blog with Author and Comments

// Get published posts with authors and published comments
const getBlogPosts = async () => {
  return await collections.posts.findMany({
    where: {
      published: { equals: true }
    },
    include: {
      author: {
        select: {
          name: true,
          avatar: true
        }
      },
      comments: {
        where: {
          status: { equals: 'published' }
        },
        orderBy: {
          createdAt: 'desc'
        },
        take: 10
      },
      _count: {
        select: {
          comments: true
        }
      }
    },
    orderBy: {
      publishedAt: 'desc'
    }
  })
}

E-commerce Product with Categories and Reviews

// Get product with all related data
const getProduct = async (slug: string) => {
  return await collections.products.findFirst({
    where: {
      slug: { equals: slug }
    },
    include: {
      categories: true,
      reviews: {
        where: {
          approved: { equals: true }
        },
        orderBy: {
          helpful: 'desc'
        },
        take: 10
      },
      variants: {
        where: {
          inStock: { equals: true }
        }
      }
    }
  })
}

User Dashboard

// Get user with all their data
const getUserDashboard = async (userId: number) => {
  return await collections.users.findUnique({
    where: { id: userId },
    include: {
      profile: true,
      posts: {
        where: {
          status: { equals: 'published' }
        },
        orderBy: {
          createdAt: 'desc' },
        take: 5
      },
      comments: {
        orderBy: {
          createdAt: 'desc'
        },
        take: 10
      },
      _count: {
        select: {
          posts: true,
          comments: true
        }
      }
    }
  })
}

Performance Considerations

N+1 Query Problem

❌ Bad - N+1 queries:

const posts = await collections.posts.findMany()
// N queries to load authors
for (const post of posts) {
  const author = await collections.users.findUnique({
    where: { id: post.authorId }
  })
}

✅ Good - Single query with include:

const posts = await collections.posts.findMany({
  include: {
    author: true
  }
})

Loading Strategies

Best Practices

Use include instead of separate queries to avoid N+1 problems
Select only needed fields from relations to reduce data transfer
Limit included relations with take when appropriate
Filter included relations to load only relevant data
Count relations with _count instead of loading all records
Index foreign keys for better join performance

// Add index for better relation query performance
export const posts = collection({
  slug: 'posts',
  fields: {
    authorId: field({
      type: relation('users'),
      indexed: true  // Index for faster joins
    })
  }
})

Next Steps

Queries - Basic CRUD operations
Filters - Advanced filtering operators
Pagination - Pagination and sorting

Querying Relations

One-to-One Relations

One-to-Many Relations

Many-to-Many Relations

Reverse Relations

Eager Loading

Lazy Loading

Selective Loading

On this page