Joins

While Relations provide a high-level, automated way to fetch related data, sometimes you need more control. Manual joins give you direct access to LEFT JOIN and INNER JOIN, allowing for custom join conditions and flattened result sets.

Use manual joins when you need to:

• Perform an INNER JOIN (to only include records that have a match in the other table).
• Filter or sort based on a column in the joined table.
• Get a flat result object instead of a nested one.
• Use complex ON conditions not supported by the relations feature.

Join Methods

The query builder provides two methods for manual joins:

• .leftJoin(table, condition, alias)
• .innerJoin(table, condition, alias)

Aliasing is Required

When you join a table, you must provide a short string alias. This alias is crucial for telling the ORM which table a column belongs to, especially in the where clause.

Example: Finding Users by Post Title

Let's find all users who have written a post with "Tauri" in the title. This is a perfect use case for an innerJoin, as we only want users who have matching posts.

import { db } from '$lib/db';
import { users, posts } from '$lib/db/schema';
import { eq, like, innerJoin } from '@type32/tauri-sqlite-orm';

const usersWithTauriPosts = await db.select(users)
  .innerJoin(
    posts,                                           // Table to join
    eq(users._.columns.id, posts._.columns.authorId), // ON condition
    'p'                                               // Alias for the 'posts' table
  )
  .where(
    like(posts._.columns.title, '%Tauri%', 'p')     // Filter on the aliased table
  )
  .all();

Explanation:

1. We start the query from the users table.
2. We innerJoin the posts table, giving it the alias 'p'. The ON condition is users.id = posts.authorId.
3. In the .where() clause, we filter on posts.title. Critically, we must pass the alias 'p' as the third argument to like() so the ORM knows to generate WHERE p.title LIKE ? instead of WHERE title LIKE ?.

Note: Manual joins return flat results. For nested data structures, use Relations with .include() instead.

The Result Shape

Unlike the nested objects from .include(), queries with manual joins return a flat object. To prevent column name collisions (e.g., both tables having an id column), the ORM prefixes each column with its table name or alias.

The result of the query above would look like this:

[
  {
    "users.id": 1,
    "users.fullName": "Jane Doe",
    "p.id": 101,
    "p.title": "Getting Started with Tauri",
    "p.authorId": 1
  }
]