Is SQL aggregation supported? SUM() COUNT() etc

Connecting to SQL
1

I am very new to DreamFactory. I have installed DreamFactory 2.0 using Bitnami installer OSX. Also create a service to connect our Salesforce.com and it works great.

When i use the endpoint to getRecords i.e /api/v2/Salesforce/_table/Opportunity. I am unable to use GROUP and ORDER successfully.

I get an error - MALFORMED_QUERY Field must be grouped or aggregated

how can i map a query like the one below to the with the params of the auto generated API endpoint.

select Campaign.Name name, SUM(amount) amount from opportunity where Campaign.Name != NULL and IsClosed=true and IsWon=true group by Campaign.Name order by SUM(amount) desc limit 5

PS: I am able to query and get response from for simple query which do not have field name aliases or SUM()

Thanks

2

Found some documentation links but still unable to successfully form the query via REST API call

Links

3

I’m not sure if these new features are supported for the salesforce service type.

@leehicks?

4

is it supported for the SQL DB service type ?

5

@viraf The features mentioned are currently working on SQL DB service types only. I know Salesforce API allows those types of calls, but are not currently available through our API.
@formerstaff Please file this as a feature request for an upcoming sprint.

6

@formerstaff @leehicks thanks. any estimates on its availability?

7

Any samples for using aggregate functions like SUM() COUNT() with SQL DB.

I m still unable to use it. :frowning:

My Query - http://localhost:8080/api/v2/sugarcrm/_table/opportunities?filter=amt%3D(SUM(amount))&order=amt%20desc&fields=amt

8

Check out the documentation on Schema and Aggregate functions.

https://wiki.dreamfactory.com/DreamFactory/Features/Database/Schema

9

@formerstaff i did check out the link you sent. Its the same link that i shared in my earlier post. I am still unable to get the aggregate functions working. :frowning:

Any help would be really appreciated.

10

@viraf Can you check that you are running version 2.0.3 (bug fix for filter functions)? If so, are you getting an error with the call or just not the correct result set requested? If an error, please post it. Also what SQL database type are you connecting to?

11

@leehicks I m now running DF 2.0.3 and still unable to get desired set. It gives me an error “Invalid field requested: amt”

My Query - http://localhost:8080/api/v2/sugarcrm/_table/opportunities?filter=amt%3D(SUM(Amount))&order=amt%20desc&fields=amt

I am using MySQL database type.

12

@viraf For starters lets check the field list. Do a call to http://localhost:8080/api/v2/sugarcrm/_schema/opportunities?refresh=true and check the field list and make sure the “amt” field is listed. The error is coming from evaluating that last part “fields=amt”. If the field is in the schema, lets try your request without that last part (not a solution just curious why it is caught there and not in the filter).

13

Firstly ‘amt’ is a virtual value holder for the aggregate function SUM(Amount) e.g amt=(SUM(Amount))

I even tried without having fields=amt as well but still get an error. :frowning:

14

@viraf Ahhh, now I see the issue. The API requires that fields be specifically be defined for security reasons and thus can not be “virtually defined” per request. What you need to do is declare “amt” to be a virtual field (see wiki and Schema tab on Admin application) and set the DB function to SUM(Amount) and be sure to select the “Aggregate function” checkbox. Then leave off the filter in your request, but include the fields=amt to just return back the aggregates.

15

@leehicks Could you please point me to possible security reasons to not allow virtually defined fields per request. Also could you please help me map my following query to the api parameters without field aliasing.

Query - SELECT SUM(Amount) FROM OPPORTUNITIES