/**
* Relationship Tools for NocoBase MCP Server
* Simplifies creating relationships between collections
*/
import { NocoBaseClient } from '../client.js';
export interface CreateRelationshipArgs {
sourceCollection: string;
targetCollection: string;
relationshipType: 'hasOne' | 'hasMany' | 'belongsTo' | 'belongsToMany';
fieldName: string;
foreignKey?: string;
reverseFieldName?: string;
through?: string; // For belongsToMany
}
/**
* Create a relationship between two collections
*/
export async function createRelationship(
client: NocoBaseClient,
args: CreateRelationshipArgs
): Promise<any> {
const {
sourceCollection,
targetCollection,
relationshipType,
fieldName,
foreignKey,
reverseFieldName,
through
} = args;
console.error(`[Relationship] Creating ${relationshipType} from ${sourceCollection} to ${targetCollection}`);
// Build field configuration based on relationship type
let fieldConfig: any = {
type: relationshipType,
name: fieldName,
target: targetCollection,
interface: getInterfaceForRelationType(relationshipType),
};
// Add type-specific configurations
switch (relationshipType) {
case 'belongsTo':
fieldConfig.foreignKey = foreignKey || `${fieldName}_id`;
fieldConfig.targetKey = 'id';
break;
case 'hasOne':
fieldConfig.foreignKey = foreignKey || `${sourceCollection}_id`;
fieldConfig.sourceKey = 'id';
break;
case 'hasMany':
fieldConfig.foreignKey = foreignKey || `${sourceCollection}_id`;
fieldConfig.sourceKey = 'id';
break;
case 'belongsToMany':
if (!through) {
throw new Error('belongsToMany requires a "through" collection');
}
fieldConfig.through = through;
fieldConfig.foreignKey = foreignKey || `${sourceCollection}_id`;
fieldConfig.otherKey = `${targetCollection}_id`;
fieldConfig.sourceKey = 'id';
fieldConfig.targetKey = 'id';
break;
}
// Create the field
const response = await client.post(
`/collections/${sourceCollection}/fields:create`,
fieldConfig
);
console.error(`[Relationship] Created field: ${fieldName}`);
// Create reverse relationship if specified
if (reverseFieldName && relationshipType !== 'belongsToMany') {
const reverseType = getReverseRelationType(relationshipType);
const reverseConfig: any = {
type: reverseType,
name: reverseFieldName,
target: sourceCollection,
interface: getInterfaceForRelationType(reverseType),
};
switch (reverseType) {
case 'belongsTo':
reverseConfig.foreignKey = foreignKey || `${fieldName}_id`;
reverseConfig.targetKey = 'id';
break;
case 'hasOne':
reverseConfig.foreignKey = foreignKey || `${sourceCollection}_id`;
reverseConfig.sourceKey = 'id';
break;
case 'hasMany':
reverseConfig.foreignKey = foreignKey || `${sourceCollection}_id`;
reverseConfig.sourceKey = 'id';
break;
}
await client.post(
`/collections/${targetCollection}/fields:create`,
reverseConfig
);
console.error(`[Relationship] Created reverse field: ${reverseFieldName}`);
}
return {
success: true,
sourceCollection,
targetCollection,
relationshipType,
fieldName,
reverseFieldName,
message: `Created ${relationshipType} relationship: ${sourceCollection}.${fieldName} → ${targetCollection}`
};
}
/**
* Get NocoBase interface type for relationship
*/
function getInterfaceForRelationType(type: string): string {
switch (type) {
case 'belongsTo':
return 'm2o'; // Many-to-One
case 'hasOne':
return 'o2o'; // One-to-One
case 'hasMany':
return 'o2m'; // One-to-Many
case 'belongsToMany':
return 'm2m'; // Many-to-Many
default:
return 'm2o';
}
}
/**
* Get reverse relationship type
*/
function getReverseRelationType(type: string): string {
switch (type) {
case 'belongsTo':
return 'hasMany';
case 'hasOne':
return 'belongsTo';
case 'hasMany':
return 'belongsTo';
default:
return 'hasMany';
}
}
/**
* Create multiple relationships at once (batch operation)
*/
export async function createRelationshipsBatch(
client: NocoBaseClient,
args: { relationships: CreateRelationshipArgs[] }
): Promise<any> {
const results = [];
const errors = [];
for (const rel of args.relationships) {
try {
const result = await createRelationship(client, rel);
results.push(result);
} catch (error: any) {
errors.push({
relationship: rel,
error: error.message
});
}
}
return {
success: errors.length === 0,
created: results.length,
failed: errors.length,
results,
errors
};
}
/**
* Quick helper: Create common CRM relationships
*/
export async function createCRMRelationships(
client: NocoBaseClient
): Promise<any> {
const crmRelationships: CreateRelationshipArgs[] = [
// Accounts → Contacts (One-to-Many)
{
sourceCollection: 'accounts',
targetCollection: 'contacts',
relationshipType: 'hasMany',
fieldName: 'contacts',
foreignKey: 'account_id',
reverseFieldName: 'account'
},
// Accounts → Opportunities (One-to-Many)
{
sourceCollection: 'accounts',
targetCollection: 'opportunities',
relationshipType: 'hasMany',
fieldName: 'opportunities',
foreignKey: 'account_id',
reverseFieldName: 'account'
},
// Contacts → Opportunities (One-to-Many, as primary contact)
{
sourceCollection: 'contacts',
targetCollection: 'opportunities',
relationshipType: 'hasMany',
fieldName: 'opportunities_as_primary',
foreignKey: 'primary_contact_id',
reverseFieldName: 'primary_contact'
},
// Leads → Users (Many-to-One, owner)
{
sourceCollection: 'leads',
targetCollection: 'users',
relationshipType: 'belongsTo',
fieldName: 'owner',
foreignKey: 'owner_id'
},
// Opportunities → Users (Many-to-One, owner)
{
sourceCollection: 'opportunities',
targetCollection: 'users',
relationshipType: 'belongsTo',
fieldName: 'owner',
foreignKey: 'owner_id'
},
// Opportunities → Leads (Many-to-One, traceability)
{
sourceCollection: 'opportunities',
targetCollection: 'leads',
relationshipType: 'belongsTo',
fieldName: 'lead',
foreignKey: 'lead_id'
},
// Events → Booth Packages (One-to-Many)
{
sourceCollection: 'events',
targetCollection: 'booth_packages',
relationshipType: 'hasMany',
fieldName: 'booth_packages',
foreignKey: 'event_id',
reverseFieldName: 'event'
},
// Events → Booth Inventory (One-to-Many)
{
sourceCollection: 'events',
targetCollection: 'booth_inventory',
relationshipType: 'hasMany',
fieldName: 'booth_inventory',
foreignKey: 'event_id',
reverseFieldName: 'event'
},
// Booth Packages → Booth Inventory (One-to-Many)
{
sourceCollection: 'booth_packages',
targetCollection: 'booth_inventory',
relationshipType: 'hasMany',
fieldName: 'inventory',
foreignKey: 'booth_package_id',
reverseFieldName: 'booth_package'
},
// Opportunities → Events (Many-to-One, for exhibition)
{
sourceCollection: 'opportunities',
targetCollection: 'events',
relationshipType: 'belongsTo',
fieldName: 'event',
foreignKey: 'event_id'
},
// Opportunities → Opportunity Items (One-to-Many)
{
sourceCollection: 'opportunities',
targetCollection: 'opportunity_items',
relationshipType: 'hasMany',
fieldName: 'items',
foreignKey: 'opportunity_id',
reverseFieldName: 'opportunity'
},
// Leads → Lead Event Interests (One-to-Many)
{
sourceCollection: 'leads',
targetCollection: 'lead_event_interests',
relationshipType: 'hasMany',
fieldName: 'event_interests',
foreignKey: 'lead_id',
reverseFieldName: 'lead'
},
// Events → Lead Event Interests (One-to-Many)
{
sourceCollection: 'events',
targetCollection: 'lead_event_interests',
relationshipType: 'hasMany',
fieldName: 'lead_interests',
foreignKey: 'event_id',
reverseFieldName: 'event'
},
// Opportunities → SOWs (One-to-Many)
{
sourceCollection: 'opportunities',
targetCollection: 'sows',
relationshipType: 'hasMany',
fieldName: 'sows',
foreignKey: 'opportunity_id',
reverseFieldName: 'opportunity'
},
// Accounts → SOWs (One-to-Many)
{
sourceCollection: 'accounts',
targetCollection: 'sows',
relationshipType: 'hasMany',
fieldName: 'sows',
foreignKey: 'account_id',
reverseFieldName: 'account'
},
// Users → Users (Self-reference for manager hierarchy)
{
sourceCollection: 'users',
targetCollection: 'users',
relationshipType: 'belongsTo',
fieldName: 'manager',
foreignKey: 'manager_id'
}
];
return await createRelationshipsBatch(client, { relationships: crmRelationships });
}
