Many-to-Many Relationships

Technical reference for the M2M feature — join table lifecycle, metadata creation, query integration, and frontend rendering.

Architecture Overview

M2M uses a Simple mode pattern: the system creates and manages a join table invisibly. Users see colored chips in the grid and a multi-select popover for editing.

Relationship Picker → RelationshipPathService (join table DDL)
    → Schema Processor (CREATE TABLE + FKs)
    → ColumnOperationHandler (metadata creation)
    → MetadataQueryBuilder (subquery in list queries)
    → M2MAssociationService (association CRUD)
    → MultiSelectSelectorComponent (frontend editor)

Join Table DDL Generation

Schema Processor (DynamicSessionFactoryBuilder + HibernateSchemaTools)

When a MANY_TO_MANY relationship is in the entity metadata:

1. extractJoinTableRequests() builds JoinTableDDLRequest objects from RelationshipMetadataDTO.joinTable()
2. buildJoinTablesDDL() generates:
   • CREATE TABLE IF NOT EXISTS with both FK columns (bigint NOT NULL)
   • Composite PRIMARY KEY on both FK columns (prevents duplicate associations)
   • Two FK constraints with ON DELETE CASCADE (referencing source + target entity tables)
3. buildDropJoinTablesDDL() generates DROP TABLE IF EXISTS for rollback

Join tables are created after the entity table DDL — ensuring both referenced tables exist.

Join Table Naming Convention

Auto-generated: {source_table}_{target_table} (sorted alphabetically for consistency)

• users + roles → roles_users (alphabetical)
• FK columns: {table}_id (e.g., users_id, roles_id)

Metadata Creation Flow

Quarkus: RelationshipPathService.createManyToManyRelationship()

1. Frontend sends POST /api/columns with relationshipType: "MANY_TO_MANY"
2. Service resolves source + target entities and auto-detects PK columns
3. Builds schema changes:
   • create_table — join table DDL
   • add_foreign_key × 2 — FK constraints (idempotent DO $$ ... END $$ blocks)
4. Builds ManyToManyCreationContext (sealed subtype of CreationContext)
5. Sends to processor via RabbitMQ

Quarkus: ColumnOperationHandler.createManyToManyMetadata()

After processor reports SUCCESS:

1. Creates Relationship entity with type=MANY_TO_MANY and joinTableName
2. Creates synthetic ColumnMetadata (_m2m_{name}, type MANY_TO_MANY)
3. Creates visible ViewColumn with widgetType=RELATIONSHIP, relationshipEditMode=ASSOCIATION
4. Creates ViewColumnPath linking to the relationship
5. Broadcasts columnCreated SSE event

List Query Integration

MetadataQueryBuilder.processM2mRelationshipColumn()

M2M columns use a correlated subquery instead of JOINs:

SELECT
  "users"."id",
  "users"."name",
  (SELECT string_agg("_m2m_t"."name"::text, ', ' ORDER BY "_m2m_t"."name")
   FROM "users_roles" "_m2m_jt"
   JOIN "roles" "_m2m_t" ON "_m2m_t"."id" = "_m2m_jt"."roles_id"
   WHERE "_m2m_jt"."users_id" = "users"."id") AS "rolesName"
FROM "users"

Key decisions:

• string_agg() produces comma-separated display values for the grid
• Correlated subquery (references "users"."id") — no GROUP BY needed on outer query
• M2M columns are not sortable (aggregate expressions)
• Alias uses buildRelationshipColumnAlias() (same as regular relationships)

Association CRUD API

M2MAssociationService + DataResource endpoints:

GET /api/data/{viewUuid}/m2m/{columnUuid}

Fetches target records with selected/unselected state for a specific source row.

Query params: rowId, search, page, size

Response:

{
  "options": [
    { "id": 1, "label": "Admin", "selected": true },
    { "id": 2, "label": "Editor", "selected": false }
  ],
  "page": 0, "size": 50, "totalElements": 5, "totalPages": 1
}

SQL pattern:

SELECT t."id", t."name",
  CASE WHEN jt."role_id" IS NOT NULL THEN true ELSE false END AS selected
FROM "roles" t
LEFT JOIN "user_roles" jt ON t."id" = jt."role_id" AND jt."user_id" = $1
ORDER BY selected DESC, t."name" ASC
LIMIT $2 OFFSET $3

POST /api/data/{viewUuid}/m2m/{columnUuid}

Adds/removes associations atomically.

Request:

{ "rowId": "42", "addTargetIds": [1, 3], "removeTargetIds": [2] }

Uses INSERT ... ON CONFLICT DO NOTHING for adds and DELETE for removes, then returns updated displayValue.

Frontend Architecture

Widget Type

RELATIONSHIP — registered in widget-types.ts (category: relation, isRelation: true)

Cell Display

Grid cells render M2M values as chips using splitM2mValue():

• Splits comma-separated string from list query
• Each value rendered as .cell-m2m-chip (M3 primary-container tokens)

Multi-Select Editor

MultiSelectSelectorComponent — standalone Angular component in relationship-editors/

Features:

• Search input with 300ms debounce
• Checkbox list with instant-save per toggle (no batch Apply)
• Selected chips at top (click × to remove)
• Pagination ("Load more...")
• Optimistic toggle with rollback on failure
• Uses M2MAssociationService for API calls (not the regular cell edit flow)

Integration: CellEditPopoverComponent routes to multi-select when column.widgetType === 'RELATIONSHIP'. The onM2mValueChange() handler emits skipApiCall: true since saves happen per-toggle via the M2M service.

SSE Sync

M2M cell updates reuse the existing view.data.cell.edited SSE event. After M2MAssociationService.updateAssociations() succeeds, the updated comma-separated display value is set on the cell. Other users receive the SSE event and see the updated chips.

Testing

Test	Type	What it verifies
ManyToManyDDLUnitTest	Unit (7 tests)	DDL extraction, generation, mixed relationships
ManyToManyJoinTableTest	Integration	End-to-end: table creation, composite PK, FKs, CASCADE, duplicates
ManyToManyColumnOperationHandlerTest	Integration	Metadata creation after migration success
ManyToManyColumnDeleteTest	Integration	Cascade cleanup of M2M metadata on column deletion

OneToMany Support

OneToMany uses the same subquery infrastructure as M2M but simpler — no join table involved.

Subquery Generation

MetadataQueryBuilder.processOneToManyRelationshipColumn()

-- Count mode (displayField=null, transform=null or COUNT)
(SELECT COUNT(*) FROM "orders" "_o2m_t" WHERE "_o2m_t"."customer_id" = "customers"."id")

-- Aggregate mode (transform=SUM, displayField=total)
(SELECT SUM("_o2m_t"."total") FROM "orders" "_o2m_t" WHERE "_o2m_t"."customer_id" = "customers"."id")

-- Chips mode (displayField set, no aggregate transform)
(SELECT string_agg("_o2m_t"."name"::text, ', ') FROM "orders" "_o2m_t" WHERE "_o2m_t"."customer_id" = "customers"."id")

Display Modes

Controlled by ViewColumn.displayField and ViewColumn.transform:

displayField	transform	Mode	SQL
null	null/COUNT	Count	COUNT(*)
null	SUM/AVG/MIN/MAX	Aggregate	SUM(field) etc
set	null	Chips	string_agg(field)

Cell Behavior

OneToMany columns are always read-only from the parent view. The isCellEditable() function returns false for isOneToMany columns.

Relationship Discovery

OneToMany relationships are included in isTraversableRelationship() and appear in the "Existing Relationships" discovery tree with a 1:N label.