Join Relationships

hypequery provides powerful features for managing table relationships and performing joins. This guide covers how to define and use join relationships effectively.

Basic Joins

You can perform joins directly in your queries:

const results = await db
  .table('orders')
  .innerJoin('users', 'user_id', 'users.id')
  .select(['orders.id', 'users.name', 'orders.amount'])
  .execute();

Join Types

hypequery supports all standard SQL join types:

// Inner Join
db.innerJoin('users', 'user_id', 'users.id')

// Left Join
db.leftJoin('users', 'user_id', 'users.id')

// Right Join
db.rightJoin('users', 'user_id', 'users.id')

// Full Join
db.fullJoin('users', 'user_id', 'users.id')

Defining Reusable Relationships

Define relationships once and reuse them across your application:

// Define relationships
QueryBuilder.setJoinRelationships({
  userOrders: {
    from: 'users',
    to: 'orders',
    leftColumn: 'id',
    rightColumn: 'user_id'
  },
  orderItems: {
    from: 'orders',
    to: 'order_items',
    leftColumn: 'id',
    rightColumn: 'order_id'
  }
});

// Use relationships in queries
const results = await db
  .table('users')
  .withRelation('userOrders')
  .withRelation('orderItems')
  .select(['users.name', 'orders.id', 'order_items.product_name'])
  .execute();