Data JOINs

Data JOINs execute on the backend in a single request. Use $data.fieldname to reference parent record fields.

joinOne (One-to-One)

Returns a single object or null. Use when you expect at most one related record.

const result = await client.queryBuilder()
  .collection('tweets')
  .joinOne('author_info', 'users')
    .onField('address').equals('$data.author')
    .selectFields(['display_name', 'avatar_url', 'verified'])
    .build()
  .selectAll()
  .execute();

// Result: { id, content, author, author_info: { display_name, ... } | null }

joinMany (One-to-Many)

Returns an array of related records. Use when you expect multiple related records.

const result = await client.queryBuilder()
  .collection('users')
  .joinMany('tweets', 'tweets')
    .onField('author').equals('$data.address')
    .selectFields(['id', 'content', 'created_at'])
    .build()
  .selectAll()
  .execute();

// Result: { address, name, tweets: [{ id, content, ... }, ...] }

Multiple JOINs

Combine multiple joins in a single query:

const result = await client.queryBuilder()
  .collection('tweets')
  .whereField('reply_to_id').isNull()
  // Author profile (one-to-one)
  .joinOne('author_info', 'users')
    .onField('address').equals('$data.author')
    .selectFields(['display_name', 'avatar_url', 'verified'])
    .build()
  // All likes (one-to-many)
  .joinMany('likes', 'likes')
    .onField('tweet_id').equals('$data.id')
    .selectFields(['user', 'created_at'])
    .build()
  // All replies (one-to-many)
  .joinMany('replies', 'tweets')
    .onField('reply_to_id').equals('$data.id')
    .selectFields(['id', 'author', 'content'])
    .build()
  .selectAll()
  .limit(20)
  .execute();

Nested JOINs

Chain JOINs before calling .build() to create nested relationships:

const result = await client.queryBuilder()
  .collection('tweets')
  .whereField('id').equals(tweetId)
  // Get replies with their authors
  .joinMany('replies', 'tweets')
    .onField('reply_to_id').equals('$data.id')
    .selectAll()
    // Nested: get author for each reply
    .joinOne('author_info', 'users')
      .onField('address').equals('$data.author')
      .selectFields(['display_name', 'avatar_url'])
      .build()
    .build()
  .selectAll()
  .execute();

Self-Referential JOINs

Join a collection to itself:

// Get tweets with quoted tweet info
const result = await client.queryBuilder()
  .collection('tweets')
  .whereField('quote_tweet_id').isNotNull()
  .joinOne('quote_tweet', 'tweets')
    .onField('id').equals('$data.quote_tweet_id')
    .selectFields(['id', 'content', 'author', 'created_at'])
    .joinOne('author_info', 'users')
      .onField('address').equals('$data.author')
      .selectFields(['display_name', 'avatar_url'])
      .build()
    .build()
  .selectAll()
  .execute();

JoinBuilder Operators

Available operators on onField():

Operator	Description
`.equals(value)`	Field equals value
`.in(values)`	Field is in array of values
`.greaterThan(value)`	Field > value
`.lessThan(value)`	Field < value
`.isNull()`	Field is null
`.isNotNull()`	Field is not null

interface Tweet {
  id: string;
  content: string;
  author: string;
  created_at: string;
  reply_to_id: string | null;
  quote_tweet_id: string | null;
}

interface User {
  address: string;
  display_name: string;
  avatar_url: string;
  verified: boolean;
}

async function getSocialFeed(limit: number = 20) {
  const result = await client.queryBuilder()
    .collection('tweets')
    // Only top-level tweets (not replies)
    .whereField('reply_to_id').isNull()
    // Get author info
    .joinOne('author', 'users')
      .onField('address').equals('$data.author')
      .selectFields(['display_name', 'avatar_url', 'verified'])
      .build()
    // Get likes count and recent likers
    .joinMany('likes', 'likes')
      .onField('tweet_id').equals('$data.id')
      .selectFields(['user', 'created_at'])
      .build()
    // Get reply count
    .joinMany('replies', 'tweets')
      .onField('reply_to_id').equals('$data.id')
      .selectFields(['id'])
      .build()
    // Get quoted tweet if exists
    .joinOne('quoted', 'tweets')
      .onField('id').equals('$data.quote_tweet_id')
      .selectFields(['id', 'content', 'author'])
      .joinOne('quoted_author', 'users')
        .onField('address').equals('$data.author')
        .selectFields(['display_name', 'avatar_url'])
        .build()
      .build()
    .selectAll()
    .orderBy('created_at', 'DESC')
    .limit(limit)
    .execute();

  return result.records.map(tweet => ({
    ...tweet,
    likesCount: tweet.likes?.length || 0,
    repliesCount: tweet.replies?.length || 0
  }));
}

Performance Tips

JOINs execute on the backend in a single request, so they are efficient. However, consider these tips for optimal performance:

Index foreign keys - Ensure fields used in JOIN conditions are indexed
Select only needed fields - Use selectFields() instead of selectAll() when possible
Limit nested data - For one-to-many JOINs, consider if you need all related records
Use materialized views - For complex queries executed frequently, create a materialized view

Getting Started

Core Concepts

Querying Data

CRUD Operations

Payments

Advanced

AI Tools

joinOne (One-to-One)

joinMany (One-to-Many)

Multiple JOINs

Nested JOINs

Self-Referential JOINs

JoinBuilder Operators

Performance Tips

Next Steps

Materialized Views

CRUD Operations

​joinOne (One-to-One)

​joinMany (One-to-Many)

​Multiple JOINs

​Nested JOINs

​Self-Referential JOINs

​JoinBuilder Operators

​Complete Example: Social Feed

​Performance Tips

​Next Steps

Materialized Views

CRUD Operations

joinOne (One-to-One)

joinMany (One-to-Many)

Multiple JOINs

Nested JOINs

Self-Referential JOINs

JoinBuilder Operators

Complete Example: Social Feed

Performance Tips

Next Steps