I’m not sure this is the best way or maybe I missed something I could have done in the interface, but this works for me.
My Scenario:
-
I have legal_documents with an M2A field linking to recordings, compositions, people, etc.
-
Users primarily work in the recordings collection or compositions,not documents
-
They need to create and link documents while editing a recording
-
Current limitation: No native way to manage this relationship from the recordings side
The Solution
I created a “hybrid” bridge table that supports BOTH M2M (strict, specific collections) and M2A (flexible, any collection) simultaneously, using database triggers to keep them in sync automatically.
Benefits:
-
M2M field on specific collections (e.g., recordings) allows creating/viewing documents directly
-
M2A field on legal_documents maintains flexibility to link to ANY collection type
-
Automatic bidirectional synchronization via database triggers
-
Single source of truth (one bridge table, no data fragmentation)
-
Both interfaces work seamlessly without user awareness of the underlying mechanism
Full Implementation Guide
This procedure is for SQLite databases. PostgreSQL/MySQL implementations would require adapting the trigger syntax.
Phase 1: Create the Hybrid Bridge Table (SQL)
Important: You cannot rely on Directus to create this table because it will make it either strict (M2M) or generic (M2A). We must manually create a table that supports both patterns.
Crucial Rule: The polymorphic item column must remain flexible and must never have a Foreign Key constraint.
BEGIN TRANSACTION;
– 1. Create the Hybrid Bridge Table
– Replace ‘legal_documents_linked_assets’ with your desired bridge table name
CREATE TABLE legal_documents_linked_assets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- The "Parent" (The Document side)
legal_documents_id INTEGER,
-- The "Flexible" Side (M2A support)
-- WARNING: Do NOT add FOREIGN KEY constraints to these columns
item VARCHAR(255),
collection VARCHAR(255),
-- The "Strict" Side (M2M support)
-- Add one column for every specific M2M relationship you need
recordings_id CHAR(36),
-- You can add more: compositions_id, people_id, etc.
-- Foreign Key constraints ONLY for specific M2M columns
FOREIGN KEY (legal_documents_id) REFERENCES legal_documents(id) ON DELETE CASCADE,
FOREIGN KEY (recordings_id) REFERENCES recordings(id) ON DELETE CASCADE
);
– 2. Auto-Sync Triggers (The Magic)
– Trigger A: When M2M writes a Recording ID → Copy to M2A columns
CREATE TRIGGER IF NOT EXISTS sync_m2m_to_m2a
AFTER INSERT ON legal_documents_linked_assets
WHEN NEW.recordings_id IS NOT NULL AND NEW.item IS NULL
BEGIN
UPDATE legal_documents_linked_assets
SET item = NEW.recordings_id,
collection = ‘recordings’
WHERE id = NEW.id;
END;
– Trigger B: When M2A adds a Recording → Copy to M2M column
CREATE TRIGGER IF NOT EXISTS sync_m2a_to_m2m
AFTER INSERT ON legal_documents_linked_assets
WHEN NEW.collection = ‘recordings’ AND NEW.item IS NOT NULL AND NEW.recordings_id IS NULL
BEGIN
UPDATE legal_documents_linked_assets
SET recordings_id = NEW.item
WHERE id = NEW.id;
END;
COMMIT;
Phase 2: Configure the M2M Side (Strict Relationship)
This sets up a “Contracts” field in the recordings collection that uses the specific column.
-
Navigate: Settings → Data Model → recordings
-
Action: Create Field → Many to Many
-
Field Key: contracts (or your preferred name)
-
Relationship Setup:
-
Select “Use Existing” junction table
-
Junction Table: legal_documents_linked_assets
-
This Collection Field: recordings_id (the strict column)
-
Related Collection Field: legal_documents_id
-
Related Collection: legal_documents
-
Save
Result: You can now create/view documents directly from the recordings interface.
Phase 3: Configure the M2A Side (Flexible Relationship)
This sets up a “Linked Assets” field in the legal_documents collection that uses the polymorphic columns.
-
Navigate: Settings → Data Model → legal_documents
-
Action: Create Field → Many to Any
-
Field Key: linked_assets (or your preferred name)
-
Relationship Setup:
-
Select “Use Existing” junction table
-
Junction Collection: legal_documents_linked_assets
-
Junction Field: legal_documents_id
-
Item Field: item
-
Collection Field: collection
-
Allowed Collections: Check recordings, people, compositions, organizations, etc.
-
Save
Result: You can attach any type of asset to documents with full flexibility.
Please let me know if you see any problems, or if I overcomplicated this.