How to create a scope that depends on a related table field

Help me!
1

Hello !

I want to do something similar to what the scope feature can do, only a little more complexe.

Basically I have a table Order which has an foreign key to the Distributor table, which again has a foreign key to the SalesRepresentative table. I want the user connected to the Forest Admin client (which is a SalesRepresentative) to only see the Order of the Distributor related to itself.

The scope functionality gives me access to the fields from the Order and Distributor collections but not from the SalesRepresentative collection.

I tried to override the GET route of the Order collection, and just execute my custom query there so I can get the data I want. It works quite fine, but the issue is that I have then to manage the pagination, filtering and sorting on my own. It seems quite complicated to completely override the GET route behavior when I only want to add a simple filter on my collection.

Do you have an easier/better way to get this done ?

I share the code I have now

router.get("/order", permissionMiddlewareCreator.list(), async (request, response, next) => {
	const { user } = request;
	const querySQL = `SELECT
	ord.id,
	...
	FROM \`order\` ord
	INNER JOIN distributor
	ON ord.distributorId = distributor.Id
	INNER JOIN salesRepresentative
	ON distributor.salesRepresentativeId = salesRepresentative.id
	WHERE salesRepresentative.email = "${user.email}"`;

	const queryType = connections.default.QueryTypes.SELECT;
	const result = await connections.default.query(querySQL, { type: queryType });
	const recordSerializer = new RecordSerializer({name: 'order'});
	const recordSerialized = await recordSerializer.serialize(result);
	response.send(recordSerialized);
});
2

Hello @JulienGaladrim,

Thanks for your message! :raised_hands:

The first thing that came to my mind was to define a direct association BelongsTo('SalesRepresentative') in your Orders definition, but I am not sure this is doable with Sequelize :thinking:

Otherwise, you could create a smart field that would define the SalesRepresentativeId on your Order, and create the scope on it (this example could be a source of inspiration).

Finally, if you want to keep the initial solution you picked, you can have a look to this example where filters, pagination and sorting are still taken into account.

Let me know!
Thanks.

3

Hello !

If I remember correctly, I already tried to create a smartf ield SalesRepresentativeId in the Order table but I couldn’t manage to use it in the scope interface.

I’ll check the example you sent and give it an other try.

Thanks !

1 Like
4

Hello again !

A little follow-up, I managed to create a smart field SalesRepresentativeEmail and to use it in a scope.

Thanks for the help !

1 Like