filter on entities with relationships

Calin

Hello,
I have read the two threads on the forum regarding multi-directional relationships and expand feature.link1, link2

Here's my case:
I have an entity which represents an activity on which multiple users are invited.
Users must respond YES/NO/MAYBE to the invitation on activity.
For this I have thought to the following setup:

Now, in my application I need to display the activities where the user has been invited.
A natural response would be to query the list of Participations where UserId = current user.
Using "expand" feature, would also bring all necessary information about each Activity.

Now, getting the list of activities in raw listing if not enough.
I need to filter/sort on Activity description and date when displaying user's activities. Also, I will need to paginate...
(the filtering must be done on server side; for a DataSource this would be serverFiltering: true)
Is there any possibility to do that? Do I need to make another setup of the database?
Do I need to embed the participations in the Activity entity to be able to filter on Activity description or date?

Calin

Thanks for your detailed response. However, as stated in the link you have provided, the filtering feature is only available when making getById ("Filtering the result of the expand expression is possible only in a "GetById" scenario.").

This doesn't corresponds to my case, as I need as response "all activities where a user is invited".
Here's the sql query like I imagine I have to make: "select * where Participation.UserId = myUserId" which will extract all ActivityId and "join" this with Activity type to get the activities.

Answer

George

Yes, your concern in this case is correct, you cannot filter the top-level data by a filter specified inside the X-Everlive-Expand header. This is due to the fact that we are not using a relational database and even when we have some relational-like features we cannot guarantee that the support will be full, thus some limitations might occur.

First of all, I would like to recommend to you to get to know how the data in Backend Services is structured and how it works in general. You can find this information here: http://docs.telerik.com/platform/backend-services/javascript/data/introduction. Based on this I would like to suggest to you to think if it is possible that you change your data's structure so it can best fit the data access/control patterns of your app. For example, you can embed the activities inside your Participations or maybe you can flip the relations?

Alternatively, if you need to keep your data schema the way it is, this scenario of yours, can still be approached with a Cloud Function. In this Cloud Function (http://docs.telerik.com/platform/backend-services/rest/server-side-logic/cloud-code/cloud-functions/introduction) you can do almost the same you have been doing in your code so far. On the server side you will need to read the data by using the same expand expression as before and when you receive the data you can filter it by excluding all items which have an Activity field of null. This way you can return only the filtered items. In your client code you need to simply change the transport of the kendo data source to the cloud function url. Please, take a look at the sample below:

Calin

I got the ideea on using the cloud code for filtering.
I think it's a good solution for small data.
The drawback with this is when having big data, it will overload the backend services servers.
The res.Results gets filtered in server's memory.
Please correct me if i'm wrong.

I have another idea (this comes in approach with your first paragraph).Solution1: keep the two types Activities and Participants.
a. Activity (editable only by the owner)
- Activity description
- Activity date
- ParticipantSummary array of UserId
b. Participation
(editable by owner/participant and activity owner). In this way a participant can change the field Response.
- ActivityId (reference to Activity)
- UserId (reference to User)
- Response (YES/NO/MAYBE)

On every add/update/delete on Participants, use a cloud method to syncronize the ParticipantSummary in Activity.

In this way, I can have a straight query directly on the Activity type, so I can get all activities for a user.
Additionaly I can filter / sort / paginate directly.

The problem with that: participants must be able to respond the the invitation with YES/NO/MAYBE.
This can be achieved using a cloud method which will modify the Activity entity using a master key.
To put some security check, I might be able to extract the user id from the request context and search if it's among the participants array.
(I mean the "request" parameter in the cloud method Everlive.CloudFunction.onRequest(function(request, response, done) {....})

The idea on these 2 approaches is to focus on fast reading/filtering, while the update of entities is a little bit slower (insignifient I would say).
A concern still remains, about concurent writing to ParticipationSummary. That's why I would stick with the Solution1...
What do you think about these approaches?

Alexander

You are correct and I am glad to see you care about the load on our servers, many users would not do that. :)

I think you are on the right track and Solution 1 would best fit your scenario. One hint - to update the ParticipantSummary field you could use Cloud code for data instead of a cloud function. Also, I would recommend you to use a Relation (multiple) field type for ParticipantSummary instead of just an array. This would trigger additional checks and ensure the values in this field are actual ids of related objects and not some random text.

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks or appropriate markings.