Hello @Guillaume_Robin
I’m sure there are multiple ways to achieve what you need.
The first one that I can think of is the following:
The documentation could be way better, we still need to work on it.
Here is some code to get you started:
In your agent customization
agent
.addDataSource(require('./virtual-datasource.ts'))
.customizeCollection('users', collection => collection.addManyToMany(...))
.customizeCollection('scenarios', collection => collection.addManyToMany(...))
In a virtual-datasource.ts file
import {
AggregateResult,
BaseCollection,
BaseDataSource,
Caller,
DataSource,
DataSourceFactory,
PaginatedFilter,
Projection,
RecordData,
} from '@forestadmin/datasource-toolkit';
class UserScenarios extends BaseCollection {
constructor(dataSource: DataSource) {
super('UserScenarios', dataSource);
const definition = {
isPrimaryKey: true,
type: 'Column',
columnType: 'Number',
filterOperators: new Set(['In' as const]),
} as const;
this.addField('userId', definition);
this.addField('scenarioId', definition);
}
async list(
caller: Caller,
filter: PaginatedFilter,
projection: Projection,
): Promise<RecordData[]> {
// Extract the userIds and scenarioIds from the filter
// This is trivial, because we know the filter is at most two IN operators with one AND
// (because that's the only operator that we support, according to the constructor)
let userIds: number[] = null;
let scenarioIds: number[] = null;
// filter.conditionTree can be null, you'll need to handle the case
filter.conditionTree.forEachLeaf(leaf => {
if (leaf.field === 'userId') userIds = leaf.value as number[];
if (leaf.field === 'scenarioId') scenarioIds = leaf.value as number[];
});
// query sequelize to get the virtual relation table
// you'll need to handle the case when either userIds is null, scenarioIds is null, or both
const records = await sequelize.query(
`
SELECT t1."userId", t2."scenarioId"
FROM groups_relation_table AS t1
INNER JOIN scenarios_relation_table as t2 ON t1."groupId" = t2."groupId"
WHERE t1."userId" IN (:userIds) AND t2."scenarioId" IN (:scenarioIds)
`,
{ replacements: { userIds, scenarioIds } },
);
return projection.apply(records);
}
create(): Promise<RecordData[]> {
throw new Error('Not supported');
}
update(): Promise<void> {
throw new Error('Not supported');
}
delete(): Promise<void> {
throw new Error('Not supported');
}
aggregate(): Promise<AggregateResult[]> {
throw new Error('Not supported');
}
}
export default function createVirtualRelation(): DataSourceFactory {
return async (): Promise<DataSource> => {
const dataSource = new BaseDataSource();
dataSource.addCollection(new UserScenarios(dataSource));
return dataSource;
};
}
Thanks a lot!