Codeless and Aggregate Functions

Server Code Codeless
1

I am going around in circles here, BUT they are very productive circles…

I have tried to append an aggregate function (max) to a service in a webbrowser but cannot get it to work…I may be missing a ? or & or ‘ or whatever…so my question is, is it possible to combine a service and an aggregate function?

Or ideally, is it possible to create aggregate functions in codeless?

The detail…

I have the below which brings in records from table = UserWorkouts.

image
image666×580 38.1 KB

The purpose is 1) to remove pagination 2) bring related data from table = Users.
But the table = UserWorkouts contains multiple records for each user and I only want to keep the best one (which can be defined as the record where (column) distance is greatest. So I have sorted the records largest to smallest on distance, BUT cannot figure out how, in Microsoft Excel terminology, to remove the duplicates.
I have been playing around with aggregate functions and I can create one that does what I need (below)…but I wont have pagination removed or have the related data.

…….data/UserWorkouts?props=Max(distance),workout,ownerId,startlevel&where=workout%3D%27BSTD%27&groupBy=ownerId

I have tried to append the aggregate function to the service in a webbrowser but cannot get it to work…I may be missing a ? or & or ‘ or whatever…so my question is, is it possible to combine a service and an aggregate function?
The below brings back the entire table…

……/findObjectsWithPaging?tableName=UserWorkouts&props=Max(%27distance%27)

Hope this is clear.
Thanks
Paul

2

Hello @Paul_McCullen

If I understand you correctly, your main problem is that there are duplicates in the query response. To avoid duplicates, set distinct=true

Regards,
Inna

3

Thanks Inna

I see (wondering what that meant…) but not entire records that are the same, just where one field (ownerId) is the same.

What logic can I put in there to just apply to one field?

Thanks
Paul

4

@Paul_McCullen

I recommend that you try to compose the desired query in our REST console. When you get the desired result, you can transfer query to the Codeless method.

Regards

5

Thanks Inna

I did think, why go into the REST console and do it?, when I have already built several functions in a web browser window using the guide below and could see it all working as expected;

But after doing it in the console and then looking at my codeless again, it was somehow obvious that the aggregate function should go in as a property. This is what I ended up with for any one else stumbling across the topic…basic in hindsight I guess :slight_smile:

image
image628×624 41.2 KB

To make this absolutely perfect, I would need one more filter on a related field but I believe this is currently not possible but will potentially be released soon. Is that correct?

Thanks
Paul

6

Could you clarify, what filter on a related field do you mean?
Conditions in the where clause ?

7

Hi Oleg

Yes, I would want a where clause on a related field. In ‘user’ I have a column called ‘Leaderboard’ which is populated with yes/no and I would filter on yes.

Thanks
Paul

8

Did you try to create the request in the REST console with desired conditions?
Something like this:
... user.Leaderboard = 'yes'

9

I love you guys…

I am sure I tried that, and then saw it mentioned it was not possible yet!? But have it now.

One more question, can I exclude related columns. I also read that in a thread as being not yet possible…

10

Ability to use related properties in where clause exists for a long time. Just try simple combination with two tables in a REST console. If you stuck with the problem, post here about the error, etc.

One more question, can I exclude related columns.

They are not returned by default. Vice versa, you have to point what relations you want to load.

11

Sorry Oleg, I wasn’t clear…but yes, I have the where clause working for the related table now in my codeless :slight_smile: It pulls through relation = user

What I meant about excluding; can I exclude some of the fields, such as userStatus as shown below?

image

12

I understood what you want.
For that just do not load relations, but use related properites.
properties list: *, user.field1, user.field2

13

Thanks a million