Automatic Relationship Filtering
Relevant source files
The following files were used as context for generating this wiki page:
Automatic Relationship Filtering is a core feature of Better Tables that allows developers to filter and sort data across joined database tables without manually writing complex SQL JOIN queries. By introspecting the database schema, Better Tables automatically detects relationships—such as one-to-one, one-to-many, and many-to-many—and generates the appropriate joins during query execution.
This system ensures end-to-end type safety, from the column definitions in the UI to the final database query. It enables dot-notation path access (e.g., profile.location) within column definitions, which the adapter resolves into efficient SQL clauses while maintaining primary-table integrity and handling pagination correctly across fanned-out result sets.
Sources: README.md:41-57, README.md:323-335, plans/011-table-definition-dx-design.md:32-55
Core Architecture and Data Flow
The relationship filtering system operates through a specialized pipeline involving schema introspection, path resolution, and query generation.
The diagram shows how a column path defined by the user flows through the detection and management layers to become a SQL query.
Sources: README.md:315-325, packages/adapters/drizzle/src/relationship-detector.ts:966-1092
Components of Relationship Management
| Component | Responsibility |
|---|---|
| Schema Introspection | Analyzes ORM schemas (e.g., Drizzle) to identify tables, columns, and relations. |
| Relationship Detector | Uses metadata, foreign keys, and naming conventions to map connections between tables. |
| Relationship Manager | Resolves dot-notation paths to specific table relationships and builds the join graph. |
| Primary Table Resolver | Identifies the starting table for a query when multiple tables are present in a schema. |
Sources: README.md:323-335, plans/030-multi-table-identity.md:58-85, plans/022-relationship-inference-honesty.md:32-42
Schema Detection and Inference
Better Tables utilizes three distinct strategies to identify how tables relate to one another, particularly within the Drizzle adapter.
Relationship Detection Strategies
- Metadata Analysis: Directly reading relationship definitions provided by the ORM schema.
- Foreign Key Discovery: Identifying explicit foreign key constraints in the database structure to map links.
- Convention-Based Guessing: If no metadata or FKs are found, the system attempts to guess relationships based on naming conventions (e.g., finding a
userIdcolumn in apoststable).
Sources: packages/adapters/drizzle/src/relationship-detector.ts:966-1092, plans/022-relationship-inference-honesty.md:44-55
The ER diagram illustrates the types of relationships Better Tables automatically detects and manages during query generation.
Sources: README.md:326-330, plans/060-derived-aggregate-columns.md:88-95
Path Resolution and Query Generation
When a developer defines a column using a path like profile.location, the RelationshipManager performs path resolution.
Path Resolution Logic
- Splitting: The path is split by segments (e.g.,
['profile', 'location']). - Mapping: The manager traverses the relationship graph starting from the primary table.
- Validation: The system checks if the final segment exists as a physical column in the target table.
- Join Generation: Optimized
LEFT JOINqueries are built based on the accessed relationships.
Sources: packages/adapters/drizzle/src/relationship-manager.ts:109-175, plans/011-table-definition-dx-design.md:83-90
Handling Fan-out and Pagination
One-to-many relationships can lead to "fan-out," where a single primary row results in multiple result rows due to joins. Better Tables handles this by:
- Detecting Fan-out: Identifying if a join path contains a "many" relationship.
- Two-Phase Pagination: Selecting a page of distinct primary keys first, then fetching the related data to ensure pages are not under-filled.
Sources: plans/020-join-pagination-underfill.md:23-45, plans/030-multi-table-identity.md:126-140
Type Safety and Inference
Better Tables uses TypeScript template literal types to provide autocomplete and compile-time validation for relationship paths.
// Example of path-typed column definition
export const usersTable = defineTable<typeof tables>()('users', (t) => ({
columns: [
t.text('name'), // Direct column
t.text('profile.location'), // One-to-one relation
t.text('posts.title'), // One-to-many relation
],
}));
Sources: plans/011-table-definition-dx-design.md:38-55, README.md:44-53
The $infer Surface
The system provides an $infer object on table definitions to extract types for rows, column IDs, and table names based on the detected relationships. This ensures that the frontend UI is always in sync with the database schema.
Sources: plans/011-table-definition-dx-design.md:162-168, plans/030-multi-table-identity.md:103-107
Summary of Automatic Relationship Filtering
Automatic Relationship Filtering eliminates the need for manual JOIN construction, reducing boilerplate and preventing common bugs like pagination under-fill or type mismatches. By leveraging ORM-specific adapters, the system maintains a high degree of technical accuracy and performance while providing a simple developer experience through dot-notation pathing.