Joins

// .join(table, left, op, right) — INNER JOIN
const posts = await Post.query()
  .join('users', 'posts.userId', '=', 'users.id')
  .select('posts.id', 'posts.title', 'users.name', 'users.email')
  .get();
// SELECT posts.id, posts.title, users.name, users.email
// FROM posts
// INNER JOIN users ON posts.userId = users.id

// .leftJoin(table, left, op, right) — LEFT JOIN
// Returns all posts, even those without a matching user
const posts = await Post.query()
  .leftJoin('users', 'posts.userId', '=', 'users.id')
  .select('posts.*', 'users.name')
  .get();

// .rightJoin(table, left, op, right) — RIGHT JOIN
// Available via advanced() (AdvancedQueryBuilder)
const data = await Post.advanced()
  .rightJoin('users', 'posts.userId', '=', 'users.id')
  .select('users.name', 'posts.title')
  .get();

// .fullOuterJoin(table, left, op, right) — FULL OUTER JOIN
// Available via advanced() (AdvancedQueryBuilder)
// Note: SQLite does not support FULL OUTER JOIN natively
const data = await Post.advanced()
  .fullOuterJoin('users', 'posts.userId', '=', 'users.id')
  .get();

// Multiple joins
const results = await Post.query()
  .join('users', 'posts.userId', '=', 'users.id')
  .join('categories', 'posts.categoryId', '=', 'categories.id')
  .select(
    'posts.id',
    'posts.title',
    'users.name',
    'categories.name'
  )
  .where('users.role', '=', 'author')
  .orderBy('posts.createdAt', 'DESC')
  .get();

// Complex join conditions with whereRaw
const results = await Post.query()
  .join('post_tags', 'posts.id', '=', 'post_tags.postId')
  .join('tags', 'post_tags.tagId', '=', 'tags.id')
  .whereRaw('tags.name IN (?, ?)', ['typescript', 'orm'])
  .select('posts.id', 'posts.title')
  .get();

// When joins are present and no .select() is called,
// SlintORM auto-qualifies root table columns to "table.*"
// to prevent "ambiguous column" errors from the database

const posts = await Post.query()
  .join('users', 'posts.userId', '=', 'users.id')
  // No .select() — SlintORM emits: SELECT posts.* FROM posts ...
  .get();