How to execute raw SQL

General
1

How can I execute raw sql? I want to use DISTINCT and COUNT. Also, how do I retrieve all records of my query without worrying about pagesize?

2

Direct DB access is not possible with Backendless Cloud.

For counts you can use the aggregate function and for distinct you add distinct=true in the request URL.

Regards,
Mark

3

I don’t understand what you mean by request URL. Am talking about using the Android sdk

This is my code

String make = “Toyota”;
String whereClause = “make = '” + make + “’”;
DataQueryBuilder queryBuilder = DataQueryBuilder.create();
queryBuilder.setWhereClause(whereClause);
Backendless.Data.of(Automotive.class).find(queryBuilder, new AsyncCallback<List>() {

        @Override
        public void handleResponse(List<Automotive> response) {

            String model = response.get(0).getModel();
	// I want to get all distinct models.How do i use the Distinct keyword?
	//And how do i get a count of distinct models?

        }

        @Override
        public void handleFault(BackendlessFault fault) {

        }
    });
4

I was talking about REST since you never mentioned anything about Android till now.

Here’s how to set distinct with Android:

queryBuilder.setDistinct( true );
5

Please provide sample code using aggregate function.

6

See the docs at COUNT - Backendless SDK for Android/Java API Documentation

7

Am assuming Count aggregates integers in a column. For example I have a column called Views, and I have 3 records with views 5 ,10 and 15. Am expecting the Count function to return 30.

After using DataQueryBuilder.create().setProperties(“Count(Views)”)

How do I get the actual count?

8

How do I get the count in the returned response object?

9

Count does what it says :wink: it counts the number of objects. You can specify objectId as the column to count objects by.

10

Sorry, it’s the SUM aggregate function I want to use.

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( “Sum(totalBoxOffice) as grandTotal” );
Backendless.Data.of( “Movie” ).find( dataQueryBuilder, new AsyncCallback<List>()
{
@Override
public void handleResponse( List response )
{
// print out the first object from the collection
Log.i( “MYAPP”, response.get( 0 ) );
}

@Override
public void handleFault( BackendlessFault fault )
{
Log.e( “MYAPP”, fault.toString() );
}
});

How do I extract “grandTotal” from the response object?

11

((HashMap)response.get( 0 )).get( “grandTotal” )