Skip to content

Data Filtering

Filtering data in database queries provides a way to narrow the set of objects which should be returned by the server. When using the aggregate functions with grouping, there are two filtering steps. The first one is by using the whereClause condition which identifies the initial set of data objects. If there is a grouping function in a query, the groups are created from that set. It is possible to apply another condition for the grouping function using the having clause. The diagram below illustrates the process:

backendlessdatafiltering.zoom90

The example below retrieves an average box office earnings for the movies which grossed more than $2 billion released after 2012 grouped by the release year. Notice the having clause which sets the condition for the groups:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Avg(totalBoxOffice)");
dataQueryBuilder.setGroupBy( "yearReleased" );
dataQueryBuilder.setHavingClause( "Avg(totalBoxOffice)>2000000000" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response );
  }

  @Override
  public void handleFault( BackendlessFault fault )
  {
    Log.e( "MYAPP", fault.toString() ); 
  }
});
Alternatively, the having clause can reference an aggregate function by the assigned alias:
DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Avg(totalBoxOffice) as averageAmount");
dataQueryBuilder.setGroupBy( "yearReleased" );
dataQueryBuilder.setHavingClause( "averageAmount>2000000000" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response );
  }

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

The having clause operates only on the group properties. These properties may include information about the related objects as well. For example, the request below counts the number of actors for the movies grouped by the release year and filters out only those where there are more than 3 actors:

DataQueryBuilder dataQueryBuilder = DataQueryBuilder.create();
dataQueryBuilder.setProperties( "Count(actors) as actorCount");
dataQueryBuilder.setGroupBy( "yearReleased" );
dataQueryBuilder.setHavingClause( "actorCount>3" );
Backendless.Data.of( "Movie" ).find( dataQueryBuilder, new AsyncCallback<List<Map>>()
{
  @Override
  public void handleResponse( List<Map> response )
  {
    // print out the first object from the collection
    Log.i( "MYAPP", response );
  }

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