How can i achieve this and get my results in table format as shown in the screenshot?
Explanation:
‘Pioneer’ is a moderator that (A)activated 25 adverts and (R)rejected 3 adverts making a (T)total of 28 adverts moderated by pioneer for today
‘moderator’ is another moderator that (A)activated 30 adverts and (R)rejected 3 adverts making a (T)total of 33 adverts moderated by moderator for today
The master table has a ‘moderatedOn’ date field and a ‘moderatedBy’ String field and ‘status’ String field
How do i get a count for each moderator to achieve the above screenshot
My first approach goes something like this
For all moderators with usernames in String arraylist
whereClause = moderatedby = username[i] AND moderatedOn today AND status = ‘active’
Get a count of this
Then do same for status = ‘rejected’
Am at a loss as how to even get the count and then aggregate all the results to form a table as shown in the screenshot.
Hello @Nkekere_Tommy_Minimann,
could you please provide your APP ID with that table scheme you’ve described here?
It’s hard to understand what is the moderated by means for the Moderator table.
Regards,
Olha
APPLICATION_ID = “C2AE98FF-1A1D-BF10-FF3E-64CD97C40D00”;
I have a table called ‘master’. This table has a string field called ‘moderatedBy’, and a date field called ‘moderatedOn’. It also has another string field called ‘status’. How do I create that table in the screenshot by aggregating the counts from these fields over a period of time eg today, this week, this month…etc
Basically I want to know how many adverts each moderator(moderatedBy) set as ‘active’, (status)and how many as ‘rejected’(status) for the specified period of time(moderatedOn)
First of all I can advise you to set the moderatedBy type to Data Relation not String:
For this schema I suggest using groupBy to retrieve amount of different statuses for each moderator.
My example is in Swift, I assume it would be very similary in Java or other languages:
let queryBuilder = DataQueryBuilder()
queryBuilder.relationsDepth = 1 // to receive moderator object for master
// in case you want to filter records in time period please use similar where clause
// e.g. this one selects records from Nov 1, 2021 to Nov 18, 2021
queryBuilder.whereClause = "moderatedOn > 1635724800000 AND moderatedOn < 1637193600000"
// adverts amount is calculated using the COUNT aggregate function
queryBuilder.properties = ["status", "Count(objectId) as adverts"]
// this grouping is made to show active and rejected adverts separately for each moderator
queryBuilder.groupBy = ["moderatedBy", "status"]
Backendless.shared.data.ofTable("Master").find(queryBuilder: queryBuilder, responseHandler: { masters in
var moderatorsInfo = [[String : Any]]()
for master in masters {
guard let status = master["status"] as? String,
let adverts = master["adverts"] as? NSNumber,
let moderatedBy = master["moderatedBy"] as? [String : Any],
let name = moderatedBy["name"] as? String,
let objectId = moderatedBy["objectId"] as? String
else { return }
// after we got adverts amount (active or rejected) for moderator, we can put that record
// to the moderatorsInfo array to use in future
if let index = moderatorsInfo.firstIndex(where: { $0["objectId"] as? String == objectId }) {
var moderator = moderatorsInfo[index]
moderator[status] = adverts
moderatorsInfo[index] = moderator
}
else {
moderatorsInfo.append(["objectId": objectId, "name": name, status: adverts])
}
}
// finally we got an array of moderators for selected dates
for moderator in moderatorsInfo {
print("[\(moderator["name"] ?? "NO NAME")] - active: \(moderator["active"] as? NSNumber ?? 0), rejected: \(moderator["rejected"] as? NSNumber ?? 0)")
}
}, errorHandler: { fault in
print("Error: \(fault.message ?? "")")
})
More info about aggregate functions can be found here: Overview - Backendless SDK for iOS API Documentation
Hope this would help you.
Regards,
Olha
This looks really good, but how do I get this in java
The first few lines look almost like their Java counterpart but later lines are difficult to translate. Who can help?
Hello @Nkekere_Tommy_Minimann
With the data structure you have now, it might look like:
private static void test()
{
DataQueryBuilder builder = DataQueryBuilder.create();
builder.setWhereClause( "moderatedOn > 1635724800000 AND moderatedOn < 1637193600000" );
builder.setProperties( "ModeratedBy", "status", "Count(objectId) as adverts" );
builder.setGroupBy( "moderatedBy", "status" );
Backendless.Data.of( "Master" ).find( builder, new AsyncCallback<List<Map>>()
{
@Override
public void handleResponse( List<Map> listObjects )
{
List<Moderator> moderatorsInfo = new ArrayList<>();
listObjects.forEach( map -> {
final String moderatorName = (String) map.get( "ModeratedBy" );
if( moderatorName == null )
return;
Moderator moderator = findModeratorByName( moderatorsInfo, moderatorName );
if( moderator != null )
{
setStatus( moderator, map );
}
else
{
Moderator newModerator = new Moderator( moderatorName );
setStatus( newModerator, map );
moderatorsInfo.add( newModerator );
}
} );
System.out.println( listObjects );
System.out.println( moderatorsInfo );
}
@Override
public void handleFault( BackendlessFault backendlessFault )
{
}
} );
}
private static void setStatus( Moderator moderator, Map map )
{
final Object status = map.get( "status" );
if( status.equals( "Active" ) )
{
moderator.setActive( ( Integer ) map.get( "adverts" ) );
}
else if( status.equals( "Rejected" ) )
{
moderator.setRejected( ( Integer ) map.get( "adverts" ) );
}
}
private static Moderator findModeratorByName( List<Moderator> moderatorsInfo, String moderatedBy )
{
return moderatorsInfo.stream()
.filter( moderator -> moderator.getName().equals( moderatedBy ) )
.findFirst()
.orElse( null );
}
Moderator.class
import lombok.Data;
@Data
public class Moderator
{
private String name;
private int active;
private int rejected;
public Moderator( String name )
{
this.name = name;
}
}
Maybe you should consider changing the database structure, something like: Master Table 1:N Table Post.
Database normalization
Regards,
Vladimir